DAX Calculate change from previous month — but Jan is different

Question

I'm looking for the DAX code to write a Power BI measure to calculate the change in value from the previous month -- with the twist that January has to be the change from the budget instead of from December I'm going to use the measure in a waterfall graph.

I used the Quick Measure to generate the code to calculate the change in value:

VAR __Prev_Month = CALCULATE([Monthly LE], DATEADD('Date'[Date].[Date], -1, MONTH)) RETURN [Monthly LE] - __Prev_Month )

But since I want the January value to be the change from the Budget (instead of from last December's Latest Estimate), I want to test for the Month Number and change the formula accordingly.

I began by inserting VAR This_month = month('Date'[Date].[Date])

but this gives me the error: A single value for variation 'Date' for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Answer 1

Looks like I got it to work with

VAR This_month - max(month('Date'[Date].[Date])

though not sure why it works for every month.