Forum Discussion
How to get average across all dates using rolling average?
try these measures
RollingAverageDirectDepositCount33Days TEST = VAR ReferenceDate = IF( ISFILTERED(Dates[Date]), MAX(Dates[Date]), // Use max selected date when filtered TODAY() // Use current date when no filter ) RETURN CALCULATE( SUM(VW_DIRECT_DEPOSIT_V10[TOTAL_DIRECT_DEPOSIT_AMOUNT]), VW_DIRECT_DEPOSIT_V10[DIRECT_DEPOSIT_YN] = "Yes", DATESINPERIOD(Dates[Date], ReferenceDate, -33, DAY) )
RollingAverageCheckingAccountsCount33Days TEST = VAR ReferenceDate = IF( ISFILTERED(Dates[Date]), MAX(Dates[Date]), // Use max selected date when filtered TODAY() // Use current date when no filter ) RETURN CALCULATE( DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]), DATESINPERIOD(Dates[Date], ReferenceDate, -33, DAY) )
Percent Total direct deposit accounts v3 TEST = VAR HasDateFilter = ISFILTERED(Dates[Date]) VAR Ratio = DIVIDE( [RollingAverageDirectDepositCount33Days TEST], [RollingAverageCheckingAccountsCount33Days TEST], 0 // Return 0 if denominator is zero ) RETURN IF( HasDateFilter, // When dates are filtered (including monthly grouping) AVERAGEX( VALUES(Dates[Date]), // Works with monthly grouping Ratio ), // When no dates are filtered AVERAGEX( ALLSELECTED(Dates[Date]), DIVIDE( [RollingAverageDirectDepositCount33Days TEST], [RollingAverageCheckingAccountsCount33Days TEST], 0 ) ) )
Thank you, but I'm looking for help in terms of these measures:
Does this work when grouped by months?
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.Okay I will give it a go. Would the same apply for balance amounts?
This works! However it made my model run a lot slower than before. Any optimization ?
Hi PowerUser2000 ,
So glad to hear that it worked. You're right though using AVERAGEX over VALUES(Dates[Date]) for a rolling measure can introduce performance overhead, especially if your date table is large or you're working with a lot of rows in your fact table.
A few ways to optimize it.
Limit the date range in visuals or model. If possible, restrict the date range using a report-level or page-level filter. For example, only load or display the past 12–24 months instead of many years. This reduces how many rows AVERAGEX has to iterate over.
Use a summarized Date table. Instead of using VALUES(Dates[Date]) which includes all dates, you can use a virtual table with only the dates that matter, like using bellow.MonthlyAvg_Rolling33DayDistinctCount = AVERAGEX(FILTER(VALUES(Dates[Date]),
Dates[Date] <= MAX(Dates[Date]) && Dates[Date] >= MAX(Dates[Date]) - 32),
[Rolling33DayDistinctCount])This narrows the iteration to only 33 dates, drastically improving performance and the result is still a valid average of the 33-day rolling counts.
Pre-calculate in a summary table (if you can). If your data volume is really high and the measure is still slow, you might consider creating a pre-aggregated summary table (like daily distinct counts) using Power Query or DAX summary tables. Then build your monthly average on top of that lightweight table.
The above steps will resolve your issue.
Regards,
Akhil.