Forum Discussion
PowerUser2000
Helper I
1 year agoCreating 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 ...
- 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
wardy912
Super User
1 year agoPercent 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