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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

Calculate the difference in month end values

Hello everyone!

I wanted to calculate the difference in sales values from month end values. For example, if my Sept 2022 MTD sales were 1000 and August 2022 MTD sales were 300, I should get a value of 700. I have various products - Biscuits, Chocolate, Drinks, etc as well as Product Categories - Food, Office Supplies, Furniture. Also, these calculations are only for each year ie, Jan 2022 should not calculate the difference of Jan 2022 values with Dec 2021.
In this case, is a calculated column better or a measure?

 

I really would appreciate any help!

Regards

3 REPLIES 3

So I solved the issue I was facing..in case anyone faces the same issue, here is my solution..

 

i created a calculated column: 

predate =
CALCULATE (
    MAX (date[Date]), FILTER ( table, date[Month] < EARLIER ( date[Month]) ) )

using this column, i created a measure to calculate previous month end values:
PreviousValue =
var _preDate =
LOOKUPVALUE(table[predate],table[preDate], LASTDATE(table[preDate]))
RETURN
 
CALCULATE(SUM(table[MTD]), FILTER(ALL(Date[Date]), Date[Date] = _preDate), REMOVEFILTERS(Date[Date].[Year]), REMOVEFILTERS(Date[Date].[Month]))
 
Hope this helps!
FreemanZ
Super User
Super User

in most cases, a measure. could you also provide some sample data?

Hi @FreemanZ 

 

DataAnalyst_99_3-1670484000115.png

 

So my data starts from Nov 1, 2021 till Nov 30, 2022. I have just provided a screenshot of month end dates sample. I need to figure the difference in MTD values for each year, with it resetting at the start of each year..

 

Regards

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.