Forum Discussion

josephc's avatar
josephc
Frequent Visitor
9 years ago
Solved

Running Total/Calculated Sum and ignoring null values at end of list

I have the following query to summarize a list of numbers and its result   mEarnedCum = CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( P...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    9 years ago

    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

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
9 years ago

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

josephc's avatar
josephc
Frequent Visitor
9 years ago

You got me there I just had to figure out what to use in the isblank.

 

I made a measure that simply counts the rows in each period, the massive aggregation of data behind the scenes elimiates the chances for mid project blanks and cleanly addresses the remaining project periods.

 

mEarnedCum = 
IF (
ISBLANK ([EarnedMHPeriodCount]),
0,
 CALCULATE (
    SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ),
    FILTER (
        ALLSELECTED ( PBIScheduleSummaryPeriod ),
        PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] )
    )
 )