Forum Discussion
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=L2ELtem2iG...
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 , 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:
Which I assumed meant there was an issue with the way I set up the date hierarchy, unless there is another reason?
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.
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.