Forum Discussion

PowerUser2000's avatar
PowerUser2000
Helper I
1 year ago

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...
Need Help
Elena_Kalina's avatar
Elena_Kalina
Solution Sage
1 year ago

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's avatar
PowerUser2000
Helper I
1 year ago
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's avatar
    Elena_Kalina
    Solution Sage
    1 year ago

    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