How to get average across all dates using rolling average?

Hi PowerUser2000 

Great question, I recently worked with a similar DAX measure and wanted to share a few insights that might help clarify things.

RollingAverageCheckingAccountsCount33Days = CALCULATE(DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]),DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -33, DAY))

This measure works perfectly when you're analyzing data at the daily level. It gives you the distinct count of account numbers over the past 33 days, based on the current date in context. However, when you try to group this by months, it behaves a bit differently.

Because  when grouped by month, MAX(Dates[Date]) picks the last day of that month, and your rolling logic applies based only on that single date not across all days in the month. So essentially, you're just seeing the 33-day count ending on the last day of each month, not a true rolling average over the month. To get a more accurate monthly picture, here's what I did is first, i kept the rolling logic per day by using bellow measure.

Rolling33DayDistinctCount = CALCULATE(DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]),DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -33, DAY))

Then, i wrapped that inside an average so that when grouping by month, it averages the daily values.

MonthlyAvg_Rolling33DayDistinctCount = AVERAGEX(VALUES(Dates[Date]), [Rolling33DayDistinctCount])

This approach gives you a much more accurate monthly number by calculating the 33-day distinct count for each day and then averaging those values across the month.

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Regards,
Akhil.