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:

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?