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
PowerUser2000
Helper I
1 year agoAlso, 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)
)