cancel
Showing results for
Did you mean:
Regular Visitor

## Difference between two columns in matrix table

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

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Regular Visitor

It works! thank you so much! 😁

Regular Visitor

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!