Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
It works! thank you so much! 😁
@amitchandak I really will appreciate if you have time to take a look on my request. Many thanks in advance.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |