Dynamically calculate difference between columns (Multi Years)Power BI

[rane19]

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

[BA_Pete]

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

[rane19]

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]))
)

 

[BA_Pete]

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

[rane19]

Pete , worked ! you are awesome . (Even I have actually stucked in the step of changing Date in power query that turns my date into 1905/7/17 e.t.c. )

[amitchandak]

@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 these

This 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]