How to create data for all periods in scorecard including most recent period, YTD, Quarter and Annual

Check list of all posts

Context
Please see attached report below
(Period /actual /target /tolerance)

Monthly data is denoted as YTD, all actual data is same for Month, Quarter and annual, however, the target data is different between YTD and annual. While the target for month YTD and Quarter data is goal accumulated to the month, the target for annual is whole year goal.  Annual metric gives business user an idea about how far is the actual from target.

Solution

The idea is to create two metrics: YTD one and Annual one. YTD metric is only calculated until current month, while annual metric is calculated until current year, or month December.  Both metrics share the same YTD source table.

Implementation

1. Create source table that contains YTD data as below, (Please see solution in detail from last post How to generate Year to Date (YTD) data for scorecard using SQL statement – an practical approach)

The current month is August 2012.  Actual data is not changed for month 9, 10, 11 and 12, while the goal is changed for month 9, 10,11 and 12.

2. Create regular metrics ( month, YTD)

Regular metrics design

3. Create annual metrics

Annual metrics design 

4. Check data  in metric studio