Forum Discussion
How to get average across all dates using rolling average?
the individual measures were working for total direct deposit accounts and total checking accounts. What is your measure helping it do?
The issue you're facing is that when no dates are selected in the slicer, your measure isn't properly calculating the average across all dates - it's just showing the value for the most recent date. The main problem is in how the rolling averages work when no date filter is applied. Your current formula tries to average the results, but the rolling average measures themselves are still only looking at the max date's 33-day window.
The solution does three key things:
It modifies the rolling average calculations so they use TODAY() as the reference date when no specific dates are selected, instead of defaulting to the maximum date. This ensures we're always looking at a proper 33-day window.
It properly averages the daily ratios across either:
The selected dates (when a date filter is applied)
All available dates (when no date filter is applied)
It uses DIVIDE() to safely handle cases where there might be zero checking accounts.
The main improvement is that now, when the dates are not selected instead of showing only the last value of the day, in fact it calculates the average value of all daily ratios, where the value of each day itself is a 33-day average.
This gives you the true overall average you're looking for, while still maintaining the rolling average behavior when specific dates are selected.
The solution fixes the script “without selected dates” to show the correct average, and not just the last meaning. Perhaps I did not quite understand what exactly you are looking for.
I understand now thank you. Am I calculating the 33 day average correctly? When I look at my numbers compared to the normal month by month basis some months are now quite a bit more than the standard start of month till end of month. I guess I don't quite understand how 33 day average works.
The differences you're observing between your 33-day rolling average and monthly averages make complete sense when we consider how these calculations work:
Continuous vs. Fixed Periods: A 33-day average updates daily and isn't tied to calendar months, while monthly averages use fixed start/end dates.
Cross-Month Inclusion: Your June average, for instance, contains data from late May, creating different comparison points.
Smoothing Effect: The longer window naturally smooths out daily fluctuations more than monthly averages.
The Seasonal Factor You Should Consider
We should also account for potential seasonality in your time patterns. While I don't know your exact data, many financial datasets show monthly patterns - often peaking at period beginnings/ends.
Here's why this matters: Your 33-day window captures two peak moments - the end of one month and start of another. When these high-value periods combine in a single average, the result naturally appears inflated compared to looking at months individually.
- Percent Total direct deposit accounts v3 TEST =VAR SelectedDates =IF(ISFILTERED(Dates[Date]),VALUES(Dates[Date]), // Use selected datesALL(Dates[Date]) // Use all dates when no filter)VAR AvgDirectDeposit =AVERAGEX(SelectedDates,[RollingAverageDirectDepositCount33Days TEST])VAR AvgCheckingAccounts =AVERAGEX(SelectedDates,[RollingAverageCheckingAccountsCount33Days TEST])RETURNDIVIDE(AvgDirectDeposit,AvgCheckingAccounts,0 // Return 0 if denominator is zero)
this measure isnt working I am getting parameter is not correct type for SelectedDates as well as it is not a valid table, variable or function name.