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 ( 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!

 

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

5 Replies

  • 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

      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's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        9 years ago

        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?

    • 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] )
    )
 )
  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    9 years ago

    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