Calculate the difference in month end values

[DataAnalyst_99]

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

[DataAnalyst_99]

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]

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

[DataAnalyst_99]

Hi @FreemanZ 

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