cumulative sum

[Nikki]

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

[Eric_Zhang]

@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]

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 · Accepted solution]

@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!

[Nikki]

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

[Jmenas]

 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? 

[BhaveshPatel]

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.