Forum Discussion
YoY % variation based on selected year
I have a DAX measure called '4w_roll_avg' that calculates the 4 week rolling average. I want to create another measure that calculates the YoY % variation based on the selection of a base year. For that, I created a parameter called 'Base YoY comparison' that displays year values from 2009 onwards and added it a slicer in my report.
So, giving this input:
| Date | 4w_roll_avg |
| 01/01/2018 | 1900 |
| 01/02/2018 | 1300 |
| 01/01/2019 | 1400 |
| 01/02/2019 | 1325 |
| 01/01/2023 | 1800 |
If I select 2019 as my 'Base YoY comparison' then I should get:
| Date | 4w_roll_avg | YoY% |
| 01/01/2023 | 1800 | 29% |
| 01/02/2023 | 1650 | 25% |
But If I select 2018 as my 'Base YoY comparison' then I should get:
| Date | 4w_roll_avg | YoY% |
| 01/01/2023 | 1800 | -5% |
| 01/02/2023 | 1650 | 27% |
Thanks
Hi roalisco ,
Here are the steps you can follow：
1. Create measure.
Measure = var _select=[Base YoY comparison Value] var _table= SUMMARIZE(ALL('Table'),[Date],"4w_roll_avg",[4w_roll_avg]) var _selectsum= SUMX( FILTER(_table,YEAR('Table'[Date])=_select&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[4w_roll_avg]) return IF( YEAR(MAX('Table'[Date]))>_select, DIVIDE( [4w_roll_avg]-_selectsum,_selectsum))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi roalisco ,
Here are the steps you can follow：
1. Create measure.
Measure = var _select=[Base YoY comparison Value] var _table= SUMMARIZE(ALL('Table'),[Date],"4w_roll_avg",[4w_roll_avg]) var _selectsum= SUMX( FILTER(_table,YEAR('Table'[Date])=_select&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[4w_roll_avg]) return IF( YEAR(MAX('Table'[Date]))>_select, DIVIDE( [4w_roll_avg]-_selectsum,_selectsum))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly