Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HChambers
New Member

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

 

HChambers_0-1625461698888.png

 

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@HChambers , 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 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@HChambers , 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 

Hey amitchandak,

 

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

HChambers_0-1625464413923.png

 

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

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors