I’m trying to calculate the average of the last three years of consumption for the same period of previous years. For example, the current Max Month is August 2022 so I’ve calculated the following:
Consump YTD Same Period Three Year Avg = VAR MaxMonth = [Max Month] RETURN CALCULATE ( [ConsumptionYTD (kgal)], Dates[MonthOfYear] <= [Max Month] )
Each year’s total is through August. Good stuff. But I can’t figure out how to filter the last three years using DAX, sum them up, and divide by a dynamic three-year interval.
Is this the right approach to the problem? I’ve tried using DATESINPERIOD rolling average approach but the result is an average for every billing date in the fact table.
This will be an intermediate calculation used in other measures.