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)
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_YEAR
Or 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)
ALLUREAN I tried the formula but didnt work. I am not sure if it didnt work because of the my calendar year table. Essentially i want calendar year slicer to only show actual years, last month and year to date. Is below calendar table causing formula to break?
My "Calendar Year" table is in below format
Calendar Year 12/31/2017 12/31/2018 12/31/2019 12/31/2020 12/31/2021 1/31/2022 2/28/2022 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
made modification to my data table and this worked.
