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

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

  • PowerUser2000's avatar
    PowerUser2000
    Helper I
    1 year ago

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