Creating moving average across all dates selected vs not selected?

[PowerUser2000]

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

Hi @PowerUser2000 , Thank you for reaching out to the Microsoft Community Forum.

I took some sample data and reproduced your scenario.

For your reference, I’ve attached the working .pbix file, please check it and share your thoughts.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[PowerUser2000]

Also, how do I get the average direct deposit amount between dates selected?

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]), -34, DAY)

)

[DataNinja777]

Hi @PowerUser2000 ,

To effectively compare a moving average across selected dates versus a broader timeframe, it's best to create a series of clear, dedicated DAX measures rather than a single, complex formula. This approach enhances readability, flexibility, and debugging. First, ensure your base rolling average calculations are correctly defined to count accounts. The rolling average for all checking accounts over a 33-day window is calculated as follows.

RollingAverageCheckingAccountsCount33Days =
CALCULATE(
    DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]),
    DATESINPERIOD(
        Dates[Date],
        MAX(Dates[Date]),
        -33,
        DAY
    )
)

Similarly, the rolling average for accounts that specifically have direct deposit requires an additional filter condition.

RollingAverageDirectDepositCount33Days =
CALCULATE(
    DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]),
    VW_DIRECT_DEPOSIT_V10[DIRECT_DEPOSIT_YN] = "Yes",
    DATESINPERIOD(
        Dates[Date],
        MAX(Dates[Date]),
        -33,
        DAY
    )
)

With these base measures, you can create a daily percentage. This intermediate measure calculates the specific ratio for each day based on the 33-day period ending on that day. Using DIVIDE is a safe way to prevent any division-by-zero errors.

Percent of DD Accounts (Daily) =
DIVIDE(
    [RollingAverageDirectDepositCount33Days],
    [RollingAverageCheckingAccountsCount33Days]
)

Now, to address your core goal, you can build two final measures for direct comparison. The first will average the daily percentage but only across the dates a user has selected in a slicer or filter.

Avg Percent DD (Selected Period) =
AVERAGEX(
    VALUES(Dates[Date]),
    [Percent of DD Accounts (Daily)]
)

The second measure provides the comparison baseline by calculating the average percentage across all dates, thereby ignoring the date slicer while still respecting other filters like branch or region. The ALL() function removes the filter context from the Dates table for this calculation.

Avg Percent DD (All Time) =
CALCULATE(
    AVERAGEX(
        VALUES(Dates[Date]),
        [Percent of DD Accounts (Daily)]
    ),
    ALL(Dates)
)

This two-measure strategy is superior because each formula has a single, clear purpose, making your model easier to manage. It gives you the flexibility to use the measures independently—side-by-side in a table, on different dashboard cards, or together in a chart where [Avg Percent DD (All Time)] can serve as a constant reference line against the dynamic [Percent of DD Accounts (Daily)]. If you still wish to have a single measure that dynamically switches its output, you can now do so by referencing the cleaner measures you've already built.

Percent Total direct deposit accounts v3 =
IF(
    ISFILTERED(Dates[Date]),
    [Avg Percent DD (Selected Period)],
    [Avg Percent DD (All Time)]
)

Best regards,

[PowerUser2000]

The filter I am using for dates is called dates[month-year] does that change anything?

[wardy912]

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