Forum Discussion

Nikki's avatar
Nikki
Helper II
9 years ago
Solved

cumulative sum

hi there i have a table called "financials". in financials there is a field called "month" (which is a date field), i also have a $ field which is called "actuals". i want to create a stacked column...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    Nikki

    MEASURE 1

    Running Total =
CALCULATE (
    SUM ( financials[actuals] ),
    FILTER (
        ALLSELECTED ( financials ),
        financials[month] <= MAX ( financials[month] )
            && MIN ( financials[month] ) <= TODAY ()
    )
)

    Measure 1 will give you the RT up to today - however you may still overshoot a bit if there's no data for the current month.

    So to address this you may want to use Measure 2.

    MEASURE 2

    Running Total 2 =
IF (
    MIN ( financials[month] )
        <= CALCULATE (
            LASTDATE ( financials[month] ),
            FILTER ( ALLSELECTED ( financials ), financials[actuals] <> BLANK () )
        ),
    CALCULATE (
        SUM ( financials[actuals] ),
        FILTER (
            ALLSELECTED ( financials ),
            financials[month] <= MAX ( financials[month] )
        )
    )
)

    Good Luck! :smileyhappy:

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago

Nikki

According to your description, you may need some measure as below. Check more details in the attached pbix.

cumulative SUM = SUMX(FILTER(ALLSELECTED(financials),financials[month]<=MAX(financials[month])),financials[actuals])

 

If it is not exactly what you're requiring, please elaborate with more details, even better with a sample pbix.

  • Nikki's avatar
    Nikki
    Helper II
    9 years ago

    thanks this is great, but how do i display cumualtive actuals though purely based on what is entered. i have forecast values till end of 2017 for each month, the report shows cumulative actuals till end of year too ..i need the cumulative actuals to stop based on when it was last entered..

    • Sean's avatar
      Sean
      Community Champion
      9 years ago

      Nikki

      MEASURE 1

      Running Total =
CALCULATE (
    SUM ( financials[actuals] ),
    FILTER (
        ALLSELECTED ( financials ),
        financials[month] <= MAX ( financials[month] )
            && MIN ( financials[month] ) <= TODAY ()
    )
)

      Measure 1 will give you the RT up to today - however you may still overshoot a bit if there's no data for the current month.

      So to address this you may want to use Measure 2.

      MEASURE 2

      Running Total 2 =
IF (
    MIN ( financials[month] )
        <= CALCULATE (
            LASTDATE ( financials[month] ),
            FILTER ( ALLSELECTED ( financials ), financials[actuals] <> BLANK () )
        ),
    CALCULATE (
        SUM ( financials[actuals] ),
        FILTER (
            ALLSELECTED ( financials ),
            financials[month] <= MAX ( financials[month] )
        )
    )
)

      Good Luck! :smileyhappy:

      • Jmenas's avatar
        Jmenas
        Advocate III
        9 years ago

         Thanks Sean you are a life saver. I had the same issue I could fix it with the second measure. By the way do you know a way to do the same thing but by predefined groups?