Forum Discussion
How to get average across all dates using rolling average?
What I want is when no dates are selected in slicer it should give the user total average percent total between direct deposit accounts and total checking accounts. Currently it is just returning the max date percent total for some reason.
This is what I have so far:
19 Replies
Based on your requirements, here's how to fix your measure to properly calculate averages across all dates when no date filter is applied, while maintaining the rolling 33-day average when dates are selected.
Percent Total direct deposit accounts v3 = VAR SelectedDates = IF( ISFILTERED(Dates[Date]), VALUES(Dates[Date]), // Use selected dates ALL(Dates[Date]) // Use all dates when no filter ) VAR AvgDirectDeposit = AVERAGEX( SelectedDates, [RollingAverageDirectDepositCount33Days] ) VAR AvgCheckingAccounts = AVERAGEX( SelectedDates, [RollingAverageCheckingAccountsCount33Days] ) RETURN DIVIDE( AvgDirectDeposit, AvgCheckingAccounts, 0 // Return 0 if denominator is zero )
Update your rolling average measures to handle the "no date selected" scenario
RollingAverageDirectDepositCount33Days = VAR ReferenceDate = IF( ISFILTERED(Dates[Date]), MAX(Dates[Date]), // Use max selected date when filtered TODAY() // Use today when no date 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 = VAR ReferenceDate = IF( ISFILTERED(Dates[Date]), MAX(Dates[Date]), // Use max selected date when filtered TODAY() // Use today when no date filter ) RETURN CALCULATE( DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]), DATESINPERIOD(Dates[Date], ReferenceDate, -33, DAY) )
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.
Thanks a lot Elena_Kalina for you time.
Use bellow dax. if you still face any issue after above sugestion.Percent Total direct deposit accounts v3 TEST =
VAR SelectedDates = IF(ISFILTERED(Dates[Date]),VALUES(Dates[Date]),ALL(Dates[Date]))
RETURN
DIVIDE(AVERAGEX(SelectedDates, [RollingAverageDirectDepositCount33Days TEST]),
AVERAGEX(SelectedDates,[RollingAverageCheckingAccountsCount33Days TEST]),0)_________________________________________________________________________________________________________________________
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.
Reagrds,
Akhil.Just checking in to see if that DAX measure worked out for you. Were you able to get the right percentage values using the rolling average logic? Let me know if anything’s still off or if you’d like help adjusting it further. Happy to support if you're still testing things.
If the above response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.
Regards,
Akhil.
