Forum Discussion
Percentage Difference Between Years
Hi Experts
What is the best way to calculate the Percentage Difference between the Years as shown in the same Data. I would like to plot the % diff in a line chart by Month
Sample Data
| Month | 2019 | 2020 | 2021 | 2022 |
| Jan | 10 | 10 | 25 | 19 |
| Feb | 25 | 13 | 24 | 25 |
| Mar | 10 | 25 | 22 | 12 |
| Apr | 13 | 24 | 13 | 14 |
| May | 20 | 16 | 24 | 24 |
| Jun | 19 | 15 | 12 | 20 |
| Jul | 16 | 23 | 23 | 21 |
| Aug | 16 | 13 | 18 | 17 |
| Sept | 11 | 23 | 20 | 19 |
| Oct | 18 | 15 | 24 | 10 |
| Nov | 17 | 21 | 10 | 23 |
| Dec | 19 | 18 | 11 | 19 |
Hi Anonymous ,
Here are the steps you can follow：
1. Select [2019] – [2022] – Transform – Unpivot Columns.
2. Change the [Attribyte] format to Whole number..
3. Create calculated column.
Flag = var _max=MAXX(ALL('Table'),'Table'[Attribute]) return IF( 'Table'[Attribute]=_max,BLANK(),VALUE(RIGHT('Table'[Attribute],2)) +1 &"-"& VALUE(RIGHT('Table'[Attribute],2)))4. Create measure.
Measure = var _current= SUMX( FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[Attribute]=MAX('Table'[Attribute])),[Value]) var _next= SUMX( FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[Attribute]=MAX('Table'[Attribute])+1),[Value]) return DIVIDE( _current,_next)5. 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
5 Replies
Hi Anonymous ,
Here are the steps you can follow：
1. Select [2019] – [2022] – Transform – Unpivot Columns.
2. Change the [Attribyte] format to Whole number..
3. Create calculated column.
Flag = var _max=MAXX(ALL('Table'),'Table'[Attribute]) return IF( 'Table'[Attribute]=_max,BLANK(),VALUE(RIGHT('Table'[Attribute],2)) +1 &"-"& VALUE(RIGHT('Table'[Attribute],2)))4. Create measure.
Measure = var _current= SUMX( FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[Attribute]=MAX('Table'[Attribute])),[Value]) var _next= SUMX( FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[Attribute]=MAX('Table'[Attribute])+1),[Value]) return DIVIDE( _current,_next)5. 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
HI Anonymous
I'm not sure I understood correctly.
Do you mean to select two years every time and display a change by month between a minimum year and a maximum year when you talk about a change in percentages?
An example of the desired result would be helpful.To plot the date by monyh you can groupby the date with date(month) & date(year)
For date diff for year
% datediff(2022-2019,2019)
If it is helpful please accept the answer.
