Forum Discussion
Dynamically calculate difference between columns (Multi Years)Power BI
Situation
I have been tried couple answers in power bi forum but not really fit my case as calculation should be dynamically calculate the differences between columns (Multi Years).
After sum up by "Matrix" table
A Measure can calculate like this :
| Group | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | Grand Total |
| A | 24 | 1136 | 30 | 10 | 136 | 30 | 1366 |
| B | 302 | 1024 | 42 | 4690 | 728 | 92 | 6878 |
| C | 24 | 1136 | 30 | 10 | 136 | 30 | 1366 |
| D | 302 | 1024 | 42 | 4690 | 728 | 92 | 6878 |
| Grand Total | 652 | 4320 | 144 | 9400 | 1728 | 244 | 16488 |
Desire Shape :
| Group | 2015 | Dif vs LY | 2016 | Dif vs LY | 2017 | Dif vs LY | 2018 | Dif vs LY | 2019 | Dif vs LY | 2020 | Dif vs LY | Grand Total |
| A | 24 | 1136 | -1112 | 30 | 1106 | 10 | 20 | 136 | -126 | 30 | 106 | 1366 | |
| B | 302 | 1024 | -722 | 42 | 982 | 4690 | -4648 | 728 | 3962 | 92 | 636 | 6878 | |
| C | 24 | 1136 | -1112 | 30 | 1106 | 10 | 20 | 136 | -126 | 30 | 106 | 1366 | |
| D | 302 | 1024 | -722 | 42 | 982 | 4690 | -4648 | 728 | 3962 | 92 | 636 | 6878 | |
| Grand Total | 652 | 4320 | -3668 | 144 | 4176 | 9400 | -9256 | 1728 | 7672 | 244 | 1484 | 16488 |
Example which doesn't work :
Difference = var MaxYear = MAX(data[YEAR]) var MinYear = MIN(data[YEAR]) RETURN CALCULATE(SUM([Value]),data[YEAR]=MaxYear) - CALCULATE(SUM([Value]),data[YEAR]=MinYear)
sample data :
#data : Group YEAR Value A 2015 12 B 2015 151 C 2015 12 D 2015 151 A 2016 568 B 2016 512 C 2016 568 D 2016 512 A 2017 15 B 2017 21 C 2017 15 D 2017 21 A 2018 5 B 2018 2345 C 2018 5 D 2018 2345 A 2019 68 B 2019 364 C 2019 68 D 2019 364 A 2020 15 B 2020 46 C 2020 15 D 2020 46 A 2015 12 B 2015 151 C 2015 12 D 2015 151 A 2016 568 B 2016 512 C 2016 568 D 2016 512 A 2017 15 B 2017 21 C 2017 15 D 2017 21 A 2018 5 B 2018 2345 C 2018 5 D 2018 2345 A 2019 68 B 2019 364 C 2019 68 D 2019 364 A 2020 15 B 2020 46 C 2020 15 D 2020 46
5 Replies
rane19 , if you want min year/max year diff, then you need first unpivot data and have data as rows
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Then you can have measure like theseThis Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))Diff = [This Year] -[Last Year]
Hi rane19 ,
Try these measures. The PY one is a bit verbose but it was the only way I could get the Prior Year to work:
_valueTY = SUM(dTable[value])_valuePY = VAR prevYear = DATE( YEAR(MAX(dTable[dtYear])) - 1, MONTH(MAX(dTable[dtYear])), DAY(MAX(dTable[dtYear])) ) RETURN CALCULATE( SUM(dTable[value]), FILTER( ALLEXCEPT(dTable, dTable[group]), dTable[dtYear] = prevYear ) )_valueDiff = [_valuePY] - [_valueTY]These measures giveme the following output when applied to a Matrix visual:
Pete
Pete , tons of thank I believe this is the answer but i couldn't reproduce the same output as the _valueDiff columns shows minus of same _vauleTY figures .
I believe the reason is date processing ? as your snapshot shows : 1 - dtYear and 2 - year , which is applied in Matrix table .
Should I add 1 more step to deal with the raw YEAR column ? (like convert from value to string )
DATE( YEAR(MAX(dTable[dtYear])) - 1, MONTH(MAX(dTable[dtYear])), DAY(MAX(dTable[dtYear])) )Hi rane19 ,
Sorry, I should have included the fact that I duplicated your [Year] column in Power Query and then changed the data type to date type to create the [dtYear] column. When you implement this in Power Query, it will change all of your years to 1st January for that year, but it allows you to use the datetime functions used in the _valuePY measure.
Pete
