Dynamically calculate YoY changes based on date selection on slicer

[dokat]

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

[ALLUREAN · Accepted solution]

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)

[dokat]

made modification to my data table and this worked.

[dokat]

@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

[ALLUREAN]

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

[dokat]

@ALLUREAN I downloaded the table however it still doesnt give me the options i am looking for. Ultimately i want user to select between below three options on slicer and entire report to update based on the seleted time frame.

Last Year

Last Month

Year To Date