Forum Discussion
Dynamically calculate YoY changes based on date selection on slicer
Hi, dokat
Try this:
YoY Variance Measure =
VAR __PREV_YEAR = CALCULATE(SUM('P&L'[Values]), DATEADD('Date'[Date], -1, YEAR)//Replace 'Date'[Date] by your date column coming from Calendar table
VAR __CURR_YEAR = SUM('P&L'[Values]) --Selected by slicer
RETURN
__CURR_YEAR - __PREV_YEAROr percent change:
YoY% Measure =
VAR __PREV_YEAR = CALCULATE(SUM('P&L'[Values]), DATEADD('Date'[Date], -1, YEAR)//Replace 'Date'[Date] by your date column coming from Calendar table
VAR __CURR_YEAR = SUM('P&L'[Values]) --Selected by slicer
RETURN
DIVIDE(__CURR_YEAR - __PREV_YEAR, __PREV_YEAR)
Try this DAX code to create calendar table. Then connect it by Date to Calendar Year of your P&L table in data model. You need to modify it to get min and max dates from your P&L table, but this is explained in the file.
https://www.dropbox.com/s/to99av1um9o7527/CalendarTableDAX.txt?dl=0
