Using a Date Hierarchy in DAX

[Anonymous]

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!

[amitchandak · Accepted solution]

@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 

[Anonymous]

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?

[amitchandak]

@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.

[Anonymous]

@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.