Difference between two columns in matrix table

[jgarcin8]

Hello

I have the following matrix visual:

I need to calculate the difference between the ammount of two dates. Ammount of 09/07/2021 less ammount of 08/30/2021 in each row.  The difference always need to be between the ammount of the last date (09/07/2021) and the ammount of the previous date (08/30/2021).

And the same situation with the field: "volume"  I need the difference between two dates, the last date (09/07/2021) and the previous date (08/30/2021).

Please Help. Thanks a lot!

[Anonymous · Accepted solution]

Hi @jgarcin8 

Is this the result you want ?

If yes ,you can refer to the steps below .

(1)Create a column to return the diff volume between 2021-09-07 and 2021-08-30 .

Diff Volume =
var previous_volume=CALCULATE(MAX('Table'[Volume]),FILTER('Table','Table'[Aging Bucket]=EARLIER('Table'[Aging Bucket]) &&'Table'[Report Date]<EARLIER('Table'[Report Date])))
var diff='Table'[Volume]- previous_volume
return diff

(2)Create a column to return the diff amount between 2021-09-07 and 2021-08-30 .

Diff Amount =
var max_date=CALCULATE(MAX('Table'[Report Date]),ALLEXCEPT('Table','Table'[Aging Bucket]))
var previous_date=CALCULATE(MAX('Table'[Report Date]),FILTER(ALLEXCEPT('Table','Table'[Aging Bucket]),'Table'[Report Date]<max_date))
var _amount=CALCULATE(SELECTEDVALUE('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Aging Bucket]),'Table'[Report Date]=previous_date))
var _diff='Table'[Amount]-_amount
return _diff

(3)Then put the two calculated column in Matrix .And the result is as shown .

 

(4)Turn off the Word wrap in Column headers and Values in Matrix Format settings ,and then hide the column that you don’t want to display in the Matrix .

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jgarcin8]

It works! thank you so much! 😁

[jgarcin8]

@amitchandak I really will appreciate if you have time to take a look on my request. Many thanks in advance.