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:

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? 

  • Nikki's avatar
    Nikki
    Helper II
    9 years ago

    Hi Sean

    Can i be a pain and ask how i can resolve an issue when the user enters $0 dollars? the cumulative doesnt pick up infact it stays where it was where the actual is great than 0. below is a screenshot of what i mean.. if i change the "BLANK" to >0 i get a cumulative till end of the year with the forecast.

    the project manager will from time to time enter and report zero actuals for that month..

     

    thanks so much in advance :):):)