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:

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:

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