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

[josephc]

I have the following query to summarize a list of numbers and its result

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

The last two periods repeat the 830 value and I was wondering if there was a way to surpress the last one.  I want to keep this as a measure so I can have running total detail, while mainintaining the 830 total value

Ideal results would have mEarnedCum = 0 or blank for PeriodEndDate 2/26/2017

Thanks!

[CahabaData]

am wondering if adding a simple parameter to your formula would do the trick

SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ),
    FILTER (
        ALLSELECTED ( PBIScheduleSummaryPeriod ),
        PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] && PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] >1

or a Filter parameter:  [ActivePeriodsEarnedMH] >1

[dedelman_clng · Accepted solution]

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]

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

[josephc]

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?