Forum Discussion
Dynamically calculate YoY changes based on date selection on slicer
Hi,
I have a large data set i am trying to do year over year calculations based on a date selection in a slicer. Below is sample datatset.
I have a slicer based on calendar year column and the columns gets updated monthly. I have both annual data and monthly data.
I need to calculate year over year sales based on Year, month or year to date selection. Can this be doable in powerbi where slicer has both calendar year, and month data and formulas automatically gets updated based on slice selection.
For Example: If Month of February 2022 selected it will automatically calculate YoY by February 2021, or Year to date Selected Jan +Feb 2022 will be divided from Jan +Feb 2021 sales total. I really appreciate if anyone can help.
The name of the table is P&L and slicer is calendar year.
| Calendar Year | P&L | Values |
| 12/31/2017 | Sales | 5,989 |
| 12/31/2018 | Sales | 4,309 |
| 12/31/2019 | Sales | 7,468 |
| 12/31/2020 | Sales | 8,790 |
| 12/31/2021 | Sales | 10,239 |
| 1/31/2021 | Sales | 992 |
| 2/28/2021 | Sales | 887 |
| 1/31/2022 | Sales | 1,190 |
| 2/28/2022 | Sales | 978 |
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)
5 Replies
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)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.
