Forum Discussion

Nikki's avatar
Nikki
Icon for Helper II rankHelper 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:

BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
9 years ago

Hi Nikki,

 

Please refer this DAXPATTERNS BLOG for the complete patterns of calculating cumulative total. It is one of hte most widely used formula in DAX and can solve many complex problems.