How to get average across all dates using rolling average?

[PowerUser2000]

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:

Percent Total direct deposit accounts v3 =

IF(

    ISFILTERED(Dates[Date]),  // Check if any date filter is applied

    // If dates ARE filtered, average only the selected dates

    AVERAGEX(

        VALUES(Dates[Date]),

        [RollingAverageDirectDepositCount33Days] / [RollingAverageCheckingAccountsCount33Days]

    ),

    // If dates are NOT filtered, average ALL dates (while respecting other filters)

    CALCULATE(

        AVERAGEX(

            ALL(Dates[Date]),

            [RollingAverageDirectDepositCount33Days] / [RollingAverageCheckingAccountsCount33Days]

        ),

        ALLSELECTED()  // Respect other filters (region, branch etc.)

    )

)

RollingAverageDirectDepositAmount33Days =

CALCULATE(

    SUM(VW_DIRECT_DEPOSIT_V10[TOTAL_DIRECT_DEPOSIT_AMOUNT]),VW_DIRECT_DEPOSIT_V10[DIRECT_DEPOSIT_YN] = "Yes",

    DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -33, DAY)

)

RollingAverageCheckingAccountsCount33Days =

CALCULATE(

    DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]),

    DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -33, DAY)

)

[v-agajavelly]

Hi @PowerUser2000 

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.

[v-agajavelly]

Hi @PowerUser2000 

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.

[Elena_Kalina]

Hi @PowerUser2000 

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)
    )

[PowerUser2000]

the individual measures were working for total direct deposit accounts and total checking accounts. What is your measure helping it do?

[Elena_Kalina]

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
            )
        )
    )

[PowerUser2000]

Thank you, but I'm looking for help in terms of these measures:

RollingAverageCheckingAccountsCount33Days =

CALCULATE(

    DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]),

    DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -33, DAY)

)
Does this work when grouped by months?

[v-agajavelly]

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.

[PowerUser2000]

This works! However it made my model run a lot slower than before. Any optimization ?

[v-agajavelly]

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.

[v-agajavelly]

Hi @PowerUser2000 ,

Just checking in to see if the optimization tips helped improve performance. If you’ve had a chance to try them out, I’d love to hear how it went. If everything’s working fine now, we’ll consider this one resolved. Otherwise, feel free to share more details we’re happy to help further.

Regards,
Akhil.

[v-agajavelly]

Hi @PowerUser2000 ,

Hope you're doing well. Just wanted to follow up once more in case you had a chance to test the optimization suggestions we discussed earlier. We totally understand things get busy, so no rush but if you're still seeing performance issues or need further tweaks, feel free to drop a note. Happy to jump in and assist further.

Thanks again,
Akhil

[PowerUser2000]

This is still slow in the report. The dataset consists of millions of rows.

[PowerUser2000]

Okay I will give it a go. Would the same apply for balance amounts?

[Elena_Kalina]

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:

1. 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.

2. 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)

3. 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.

[PowerUser2000]

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.

[Elena_Kalina]

The differences you're observing between your 33-day rolling average and monthly averages make complete sense when we consider how these calculations work:

1. 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.

2. Cross-Month Inclusion: Your June average, for instance, contains data from late May, creating different comparison points.

3. 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.

[PowerUser2000]

I'm using your rolling average calculations but I'm grouping my visual by month. It should still work though correct? Or no 

[PowerUser2000]

Percent Total direct deposit accounts v3 TEST =

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 TEST]

    )

VAR AvgCheckingAccounts =

    AVERAGEX(

        SelectedDates,

        [RollingAverageCheckingAccountsCount33Days TEST]

    )

RETURN

    DIVIDE(

        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.

[Elena_Kalina]

Hi @PowerUser2000 

Try this one 

Percent Total direct deposit accounts v3 TEST =
VAR HasDateFilter = ISFILTERED(Dates[Date])
VAR Ratio = 
    DIVIDE(
        [RollingAverageDirectDepositCount33Days TEST],
        [RollingAverageCheckingAccountsCount33Days TEST],
        0
    )
RETURN
    IF(
        HasDateFilter,
        Ratio,  // When dates are filtered, show the direct ratio
        AVERAGEX(
            ALLSELECTED(Dates[Date]),
            DIVIDE(
                [RollingAverageDirectDepositCount33Days TEST],
                [RollingAverageCheckingAccountsCount33Days TEST],
                0
            )
        )
    )

If it is not suitable either, then maybe you should share with me an example of your report so that it is easier for me to help you