Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Calculating Difference between two columns in a matrix

Hello, 

 

I am looking to calculate the numerical difference (in one column) and % difference (another column) between the last two dates for each row: 31st July and 17th August.

 

 

soochoilondon_0-1661070067076.png

 

The values in this table are from one measure which is linked to a FX conversion table (below is the screenshot measure), filtered by date and Financial Partner.

 

soochoilondon_1-1661070182110.png

 

Thank you. 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , With help from the date table, you can use this month vs last month, I am assuming you are getting 17 -Aug because that is the last date in August you have data

 

example

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

You can also explore lastnonblankvalue, along with MTD

 

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

You could create a measure.

 

Measure = 
var _second=CALCULATE(MAX('Table'[date]),FILTER('Table',[date]<MAX('Table'[date])))
return 
IF(HASONEVALUE('Table'[date]),[Total in gdp],
CALCULATE([Total in gdp],FILTER('Table',[date]=MAX('Table'[date])))-CALCULATE([Total in gdp],FILTER('Table',[date]=_second)))

 

The final show:

AilsaTao_0-1661308417533.png

If the above one can't help you get the desired result, It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , With help from the date table, you can use this month vs last month, I am assuming you are getting 17 -Aug because that is the last date in August you have data

 

example

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

You can also explore lastnonblankvalue, along with MTD

 

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.