topic Re: Using a Date Hierarchy in DAX in DAX Commands and Tips

Using a Date Hierarchy in DAX

Anonymous — Mon, 05 Jul 2021 05:08:46 GMT

Hi All,

Apologies in advance, I am very new to Power BI and still trying to learn as much as I can.

I am trying to follow the example provided here: https://www.youtube.com/watch?v=L2ELtem2iGY&t=213s, more specifically the code shown below:

% Change = VAR CurrentValue = [Total Tickets] VAR PreviousValue = SWITCH( TRUE(), ISINSCOPE('Date'[Date].[Day]), CALCULATE([Total Tickets]), DATEADD('Date'[Date], -1, DAY), ISINSCOPE('Date'[Date].[Month), CALCULATE([Total Tickets]), PARALLELPERIOD('Date'[Date], -1, MONTH), ISINSCOPE('Date'[Date].[Quarter], CALCULATE([Total Tickets]), PARALLELPERIOD('Date'[Date], -1, QUARTER), ISINSCOPE('Date'[Date].[Year], CALCULATE([Total Tickets]), PARALLELPERIOD('Date'[Date], -1, YEAR) ) RETURN DIVIDE( CurrentValue - PreviousValue, PreviousValue )

However, I am struggling to be able to create a Hierarchy in my Date table that allows me to use a hierarchy in the following way: 'Date'[Date].[Day]'. Basically, I am unable to select the hierarchy I created in my DAX expression.

The Hierarchy I have created is pictured below, and I am aware that it is named differently from the example above, however, I am still unable to select it ('Date'.[Date Hierarchy].[Day] etc.).

I have attempted to resolve this through multiple methods and none seem to work, so any help would be greatly appreciated!

Re: Using a Date Hierarchy in DAX

amitchandak — Mon, 05 Jul 2021 05:48:19 GMT

Anonymous , You can direct columns? If need you can put them all in the slicer

% Change = VAR CurrentValue = [Total Tickets] VAR PreviousValue = SWITCH( TRUE(), ISINSCOPE('Date'[[Day]), CALCULATE([Total Tickets], DATEADD('Date'[Date], -1, DAY)), ISINSCOPE('Date'[Month), CALCULATE([Total Tickets], PARALLELPERIOD('Date'[Date], -1, MONTH)), ISINSCOPE('Date'[Quarter]), CALCULATE([Total Tickets], PARALLELPERIOD('Date'[Date], -1, QUARTER)), ISINSCOPE('Date'[Year]), CALCULATE([Total Tickets], PARALLELPERIOD('Date'[Date], -1, YEAR)) ) RETURN DIVIDE( CurrentValue - PreviousValue, PreviousValue )

Try if the above can work

Re: Using a Date Hierarchy in DAX

Anonymous — Mon, 05 Jul 2021 05:54:22 GMT

Hey amitchandak,

I have tried that before but I just get this error message instead:

Which I assumed meant there was an issue with the way I set up the date hierarchy, unless there is another reason?

Re: Using a Date Hierarchy in DAX

amitchandak — Mon, 05 Jul 2021 06:23:56 GMT

Anonymous , when I give this formula, I corrected the position of the parenthesis. Make sure isincope is closed also calculate ending before next is in scope.

Re: Using a Date Hierarchy in DAX

Anonymous — Mon, 05 Jul 2021 06:47:27 GMT

amitchandak my bad! I did not notice that. Just fixed it and it is now functioning as expected! Thank you so much for helping out, really appreciate it.