Multiple hierarchies and xTDs


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 Iwork 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 tomyself: 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 beensolved byusing to Date tables in PowerPivot. One table containing the standard calendar and one containingthe year-week data. Both will get a hierarchy and are linked to the same fact table using the same date key.

Diagram view

And in the fact table two values are defined: YTDMonth and YTDWeek using both a different datetable. 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 asthere 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

DAX Excel PowerPivot


Jan Pieter Posthuma Profile Picture
Jan Pieter Posthuma

My name is Jan Pieter Posthuma and I am a Microsoft Data Analytics consultant working for Rubicon, a local consultancy firm in The Netherlands.


Share This Post
Twitter Google+ Pinterest Facebook