Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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)
Proud to be a Super User!
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)
Proud to be a Super User!
made modification to my data table and this worked.
@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
Proud to be a Super User!
@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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |