Last week I was challenged by a co-worker: *‘create a way that a tabular cube case serve a YTD value for different time hierarchies.’ *Because I work at a retailer at this moment, there is a fiscal calendar and a 454 calendar, which of course doesn’t align in the begin and end date.
While I was writing this post, Marco Russo wrote a great post on how to createdifferent xTD values. Please read it: http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ (Note to myself: do not postpone the writing of your posts)
This has one advantage for me: I don’t have to explain the logic behind xTD who are not based on a standard calendar. Lucky me :-).
The only challenge left is the one measure with different hierarchies, but that can easily been solved by using to Date tables in PowerPivot. One table containing the standard calendar and one containing the year-week data. Both will get a hierarchy and are linked to the same fact table using the same date key.
And in the fact table two values are defined: YTDMonth and YTDWeek using both a different date table. And the combined is a measure with a IF() statement showing one of the two YTD values.
YTD:=IF(HASONEVALUE('Calendar Week'[Year]), [YTDWeek],[YTDMonth])
Basically it is the same situation as there was with Analysis Service 2000: per dimension one hierarchy. And as long as DAX is not aware of the dimension that is selected or scoping, this design paradigm should be used to solve this problem. Or stick with a value per xTD.
-JP
Test file: Multiple hierarchies with one YTD measure