Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using a Date Hierarchy in DAX

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!

  • 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 

4 Replies

  • 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's avatar
      Anonymous
      Not applicable

      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's avatar
        amitchandak
        Super User

        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.