Forum Discussion
How to calculate the difference between columns in matrix
I almost found a solution for this one:
As amitchandak suggested, I created an index column here is the data including the index
Then using this measure to calculate the difference from this thread (https://community.powerbi.com/t5/Desktop/Calculate-the-difference-between-two-columns-without-hard-coding/m-p/505691#M236099 )
Diff =
VAR currIndex = MAX ( Data[Index] )
VAR currValue =
CALCULATE (
SUM ( Data[#] ),
FILTER ( ALLSELECTED( Data ), Data[Index] = currIndex),
VALUES ( Data[Type])
)
VAR preValue =
CALCULATE (
SUM ( Data[#] ),
FILTER ( ALLSELECTED( Data ), Data[Index] = currIndex -1),
VALUES ( Data[Type])
)
RETURN
IF ( preValue <> BLANK (),currValue - preValue, "-" )
Here is the results:
This is almost what I am looking for, however, if we apply a filter on the period, it does not work:
This is because they are not consecutive periods ( it works fine if they are consecutive periods).
I tried to modify the measure to solve this issue:
It actually works fine when the filter is applied:
But it does not work when the filter is removed:
As you can see, it calcuate the fieernce between a certain column and the sum of all previous columns
So, Is there any way to modify the measure to solve this issue (respecting the filter applied).
Thank you in advance.