Hi @AppleBoy ,
It seems you're encountering a common filter context issue in DAX. To create a rolling 3-month average, you need a measure that can override the visual's context for a single month and apply its own date range. The solution is to use the CALCULATE function in combination with DATESINPERIOD to define this rolling window. This approach will also correctly handle the cumulative average for the first few months of your data automatically.
You can use the following DAX measure. Remember to replace YourTable[Working_Hours] with the actual names of your table and column, and 'Calendar'[Date] with the date column from your dedicated Calendar table.
Rolling 3-Month Avg Working Hours =
VAR LastDate =
MAX ( 'Calendar'[Date] )
VAR Period =
DATESINPERIOD (
'Calendar'[Date],
LastDate,
-3,
MONTH
)
VAR Result =
CALCULATE (
AVERAGE ( YourTable[Working_Hours] ),
Period
)
RETURN
Result
This measure works by first capturing the last date in the current filter context (e.g., April 30th for the "April" row in your visual) using the LastDate variable. It then uses this date in the DATESINPERIOD function to generate a table of dates that spans the previous three months, including the current one. Finally, CALCULATE evaluates the AVERAGE of your Working_Hours column, but it replaces the visual's single-month filter with the new 3-month Period we just defined. This logic inherently handles the cumulative aspect at the beginning of your dataset; for the second month of data, it will average the first two months, and only from the third month onwards will it operate as a true rolling three-month calculation.
To implement this correctly, ensure you have a dedicated Calendar table marked as a date table in your model. There must be an active one-to-many relationship from this Calendar table's date column to the date column in your data table. Once that is set up, you can create a new measure in your table and paste this DAX code. After adjusting the names, you can add it to your visuals to see the correct rolling average.
Best regards,
