How to calculate change from previous month -- but only within the same year

[Anonymous]

I'm looking for the DAX code to write a Power BI measure to calculate the change in value from the previous month. But I don't want January to show the change since December; instead, I want January to show the difference from the budget amount. 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:

LE Difference = 
     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), my plan is to test for the Month Number and change the formula accordingly. I tried 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.

And I suspected that this would happen, but I don't remember how to work around it.

[CNENFRNL · Accepted solution]

Hi, @Anonymous , from syntax's perspective, you might want to try,

VAR this_month = MONTH( MAX('Date'[Date].[Date]) )

[Anonymous]

Yes, that worked, as did

   This_month = max('Date'[Date].[Month])

though I'm not sure why the max month is able to change for each iteration.

[CNENFRNL]

As a matter of fact, MAX('Date'[Date].[Month]) is a syntactic sugar for MAXX('Date', 'Date'[Date].[Month]); here 'Date' table is subject to changes of filter context. During iteration, filter context changes; MAX() changes accordingly.