cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jgarcin8
Regular Visitor

Difference between two columns in matrix table

Hello

I have the following matrix visual:

jgarcin8_1-1631655167980.png

 

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!

1 ACCEPTED SOLUTION
Ailsa-msft
Community Support
Community Support

Hi @jgarcin8 

Is this the result you want ?

Ailsamsft_0-1631860627052.png

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 .

Ailsamsft_1-1631860627053.png Ailsamsft_2-1631860627055.png

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

Ailsamsft_3-1631860627056.png

The final result is as shown :

Ailsamsft_4-1631860627057.png

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.

View solution in original post

3 REPLIES 3
Ailsa-msft
Community Support
Community Support

Hi @jgarcin8 

Is this the result you want ?

Ailsamsft_0-1631860627052.png

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 .

Ailsamsft_1-1631860627053.png Ailsamsft_2-1631860627055.png

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

Ailsamsft_3-1631860627056.png

The final result is as shown :

Ailsamsft_4-1631860627057.png

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.

View solution in original post

It works! thank you so much! 😁

jgarcin8
Regular Visitor

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!