I need to create the below table showing '% on track' which is displayed against countries. The Year-Month showed on each column are from Calendar dimension. I have used Calendar table as a data Island because I have more then 40 dates present in my datawarehouse. Now I have a problem creating YTD.
YTD is calcuated only for current year irrespective of calendar Month Year selection. It means that if user select Year as 2011 and month as June to Dec, the YTD still should be calculated for Jan-12 to Jun-12(current month).
% On Track
12-Jan
12-Feb
12-Mar
12-Apr
12-May
12-Jun
12-Jul
12-Aug
YTD
India
10%
11%
12%
13%
14%
34%
35%
43%
45%
USA
65%
11%
12%
23%
14%
23%
1%
43%
41%
UK
11%
34%
12%
13%
13%
34%
35%
43%
99%
Summary
62%
11%
12%
25%
12%
23%
51%
42%
49%
Also the Summary row is showing the sub-total of all the percentages which is wrong and should be calculated across the countries for each month.
I have attached the qvw and related dummy data explaining my problem.
