DAX Measure – Getting Difference between 2 values in a table
I had a requirement where the customer wanted the difference between 2 values in a table.
The challenge here was that because the values are already part of the table, I had to find a way to get the unique value for each item. This would then allow me to calculate each value separately and then calculate the difference.
This is what the final output looked like, and below is the DAX measure that I created to get the difference.
As my good friend Matt always says break it down into smaller parts to solve the puzzle. In this instance I know that I needed to have two measures, where one would be filtered for the first year and the second one would be filtered to the last year.
Difference between Years = VAR AllYears = CALCULATE ( CONCATENATEX ( VALUES ( 'Table1'[Year] ), 'Table1'[Year] , "," ) ) VAR FirstYear = RIGHT ( AllYears, 4 ) VAR LastYear = LEFT ( AllYears, 4 ) VAR FirstYearAmount = CALCULATE ( [Sales], 'Table1'[Year] = FirstYear ) VAR LastYearAmount = CALCULATE ( [Sales], 'Table1'[Year] = LastYear ) RETURN FirstYearAmount – LastYearAmount
Here is the explanation of my measure

Lines 2 – 9
 I am getting a concatenated list of all the years that are selected separated by the comma
 EG: For 2017 and 2018 it is returning the following.

Lines 10 – 11
 This is where I am getting the first year from my concatenated list of years

Lines 12 – 13
 This is where I am getting the last year from my concatenated list of years

Lines 14 – 18
 This is where I am creating my measure which will be for the firstyear amount

Lines 19 – 23
 This is where I am creating my measure which will be for the lastyear amount

Lines 24
 This allowed me now to subtract one measure from the other.
 And return the final result
This will currently work, if multiple years are selected it will always select the first and last year from the entire selection.
As always, I hope that you enjoyed this blog, and if you got another way to get the same result I would be interested in hearing about it in the comments below.
…Min/Max was not an option?
I did try that, but it did not appear to give me the results I required.
Can I change the granularity of the data calculated for same logic.
Hi Suraj,
Yes this should be possible, and thanks for the comments.
Hi, Thanks for helping out on Power BI
How to calculate the difference between columns in the matrix table in power BI? and fix remove the duplicated columns in each of the subcategories.
I used the below DAX but somehow it’s duplicated the calculated column “Delta TMCGP%” in all the subcategories. when I just want to show it at the end of the matrix table.
Delta TEST =
VAR TMCGPpctCWV (CURRENT) =
DIVIDE (
SUM ( BC_Dashboard_V4_Standard[CWV_TMCGP] ),
SUM ( BC_Dashboard_V4_Standard[CWV_REVENUE] )
)
VAR TMCGPpctPOR (PREVIOUS)=
DIVIDE (
SUM ( BC_Dashboard_V4_Standard[POR_TMCGP] ),
SUM ( BC_Dashboard_V4_Standard[POR_REVENUE] )
)
VAR Result = TMCGPpctCWV – TMCGPpctPOR
RETURN
Result
—
In Tableau, I use a formula similar to the below.
IF ATTR([TYPE] ‘6_DELTA’) THEN (sum([TMCGP])/sum([UNITS]))
ELSE (sum([CWV_TMCGP])/sum([CWV_UNITS])) – (sum([POR_TMCGP])/sum([POR_UNITS]))
END
Hi Ian, thanks for the comment
What you could do is create one more VAR where it would be the following:
VAR LastResult = IF(ATTR[TYPE] = '6_DELTA', (sum([TMCGP])/sum([UNITS]), Result)
RETURN
Last Result
Thank you for your response and your help is much appreciated.
The ATTR[TYPE] in my column it is called “Type (Group)” (in it contains 4_CWV, 5_POR, 6_Delta) but I can not get it to populate to have it filter one of these groups in the dax formula creation.
In general, I want to just calculate the two columns in the matrix table from Power BI, I have yet to figure out this delta because I am unable to filter it out.
example
below is my slicer to switch Category of IDs.
KPF Category =
SWITCH(TRUE(),
SEARCH(“CHIPSET”,’KPF_COST_ID (unpivot)'[Attribute],,0)>0,”CHIPSET”,
SEARCH(“MEMORY”,’KPF_COST_ID (unpivot)'[Attribute],,0)>0,”MEMORY”,
SEARCH(“DISPLAY”,’KPF_COST_ID (unpivot)'[Attribute],,0)>0,”DISPLAY”,
SEARCH(“FRONT”,’KPF_COST_ID (unpivot)'[Attribute],,0)>0,”F.CAMERA”,
SEARCH(“REAR”,’KPF_COST_ID (unpivot)'[Attribute],,0)>0,”R.CAMERA”,
SEARCH(“FPS”,’KPF_COST_ID (unpivot)'[Attribute],,0)>0,”FPS”,
SEARCH(“BATTERY”,’KPF_COST_ID (unpivot)'[Attribute],,0)>0,”BATTERY”,
SEARCH(“CHARGER”,’KPF_COST_ID (unpivot)'[Attribute],,0)>0,”CHARGER”,
“N/A”
)
in the matrix table (before I had to unpivot the table so that I can create this dynamic slicer. In tableau it’s called parameter), i have
program name  Before Date  After Date  (Value base on the slicer) Chipset ID (Before)  Chipset ID (After)
How would I create a formula to create:
1. IF ( column (Before) = column (After) , “T”, “F”)
2. same as for delta IF ( column (Before) = column (After), sum(before – after), “no change”)
For further clarity please take a look at my posting in stackoverflow which will include screenshots https://stackoverflow.com/questions/66805902/calculatedifferencebetweencolumnsubcolumnsinmatrixreportpowerbi