Forum Discussion

PowerUser2000's avatar
1 year ago
Solved

Creating moving average across all dates selected vs not selected?

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 ...
  • wardy912's avatar
    1 year ago
    Percent Total Direct Deposit Accounts v4 =
    
    VAR IsDateFiltered = ISFILTERED(Dates[Date])
    RETURN
    IF(
        IsDateFiltered,
        AVERAGEX(
            VALUES(Dates[Date]),
            DIVIDE(
                [RollingAverageDirectDepositCount33Days],
                [RollingAverageCheckingAccountsCount33Days]
            )
        ),
        CALCULATE(
            AVERAGEX(
                ALL(Dates[Date]),
                DIVIDE(
                    [RollingAverageDirectDepositCount33Days],
                    [RollingAverageCheckingAccountsCount33Days]
                )
            ),
            ALLSELECTED(Dates)  // Keeps other slicers like region, branch, etc.
        )
    )

     

    • DIVIDE() is better than / to avoid divide-by-zero errors.
    • ALLSELECTED(Dates) ensures that other slicers are respected even when no date is selected.
    • ALL(Dates[Date]) ensures the full date range is used when no date filter is applied.

     

    Please give a thumbs up if this helps