Use Max Date Month From Fact Table in Prior Years in Matrix

[BITomS]

Hi all,

This problem is a blocker to a wider calculation (YTD measure), but am just showing this specific part in the hope to make it easier to resolve.

I have a number of years in my matrix table and want to identify the Max Month in the current year (i.e. May 2024 which is showing correctly as 5 below), but also apply this same month to previous years to make it dynamic. However, for the previous years, the YTD month returned is for the full year i.e. 12.

Please may someone help me understand how I can ensure 2022 and 2023 years also show 5 instead of 12?

FYI, the max date is taken from a fact table (DailyConsumptions), not a Date dimension table, although a date dimension table does exist in the model.

[BITomS · Accepted solution]

Fixed it myself:

VAR _Month = MONTH(calculate(max(DailyConsumptions[Date]),removefilters('Date')))