Hi All,
One of our main requirements is to be able to report a weeks worth of data from one year against a weeks worth of data from another year (not always the previous year so we need full flexibility, even the week numbers may be different). For example:-
2012.05 vs 2011.05
2012.02 vs 2010.14
I have created a master calendar which contains calendar date, year, month, week, day, year+week fields.
The data table that contains sales date etc contains the calendar date field so that it is joined to the master calendar.
No matter which i try to pull of year+week data (as in the examples above) I constantly get nothing returned.
For example using the year and week fields seperately in the expression:-
=Sum ({$<CALENDARYEAR={$(vYear1)},CALENDARWEEK={$(vWeek1)>}[SALES-EXTAX])-Sum ({$<CALENDARYEAR={$(vYear1)},CALENDARWEEK={$(vWeek1)>}[COSTOFGOODS])
Another attempt has been using concatenated variables that contain YYYY.WW in their value:-
=Sum ({$<CALENDARYEARWEEK={$(vYearWeek1)}>}[SALES-EXTAX])-Sum ({$<CALENDARYEARWEEK={$(vYearWeek1)}>}[COSTOFGOODS])
Neither works and I have no idea why, and I'm starting to pull my hair out!
Does anyone have any suggestions as to either what I'm doing wrong, or be able to point me in the right direction.
Many Thanks
Trevor
