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