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 and line graph report which displays cumulatives over years/or months . I have added "month" as a page filter so the user can choose.

could somebody please help me the sum/query for the column or measure etc?

i have already created a nice report which displays spend per month (but cumulative would be awesome).

 

i tried to review other posts but they didnt make much sense to me sorry :(

 

desired result below

thanks
N

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:

6 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super 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.

     

     

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