Forum Discussion
Running Total/Calculated Sum and ignoring null values at end of list
Try wrapping an IF looking at ActivePeriodsEarnedMH and if it is blank (ISBLANK) return 0
mEarnedCum = IF ( ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]), 0, CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( PBIScheduleSummaryPeriod ), PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] ) ) ) )Hope this helps
David
Community ChampionTry wrapping an IF looking at ActivePeriodsEarnedMH and if it is blank (ISBLANK) return 0
mEarnedCum =
IF (
ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]),
0,
CALCULATE (
SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ),
FILTER (
ALLSELECTED ( PBIScheduleSummaryPeriod ),
PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] )
)
)
)Hope this helps
David
It is a measure, so the, ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]),0 is throwing the typical a singe value for column x cannot be determined, since an aggregate function is not specified.
So no luck there, good suggestion for a column though, but I need the inherit behavior of the measure for this to work
- dedelman_clng
This looks similar to an exercise I did in a DAX class. In that case the measure was compared to 0 as the "IF" condition, and if <> 0 the calculation took place, else blank (the else was actually left out of the solution since it defaults to blank)
mEarnedCum = IF ( [ActivePeriodsEarnedMH] <> 0, CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( PBIScheduleSummaryPeriod ), PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] ) ) ) )If this doesn't work, can you share an anonymized version of your pbix file?