Cumulative Amounts

[PBJ]

Hi everyone!

I am trying to calculate the cumulative sums of amounts in different months within a year. In excel, I would do so by locking the first value in the first month of interest and then calculate the following months/values by adding the previous figures to them.

For instance, the amounts in C4 is the result of =SUM($B$2:B4)

	year	cumulative year
July	100000	100000
August	35975	135975
September	23423	159398

How can I perform the same calculation in PowerBi-what would be the appropriate command?I have a much lengthier list and in excel I would use the SUMIFs formulat to aid the operation.

[v-haibl-msft]

@PBJ

If your data table is similar to below one. You should get the expected result with following measure provide by KGrice.

If you still cannot get it to work, could you please provide some sample data to us so that we can give an exact formula?

Running Total = 
CALCULATE (
    SUM ( Table2[Value] ),
    FILTER ( ALL ( Table2 ), Table2[Date] <= MAX ( Table2[Date] ) )
)

Best Regards,

Herbert

[PBJ]

Super thanks for all of your comments, appreciate it. I eventually managed to work it out with TOTALYTD. But I also had to fix the date measure, as it was highlighted in your answers. Without a proper date, in fact, I was not able to pick up the right data - this is so as eventually my data set ended up having multiple years.

Thank you all for your input 🙂

[v-haibl-msft]

@PBJ

The measure provided by KGrice should be OK. You can also try to create a MonthNum column and a cumulative year column.

MonthNum = 
MONTH ( LEFT ( Table1[month], 3 ) & "/1" )

cumulative year = 
SUMX (
    FILTER ( Table1, Table1[MonthNum] <= EARLIER ( Table1[MonthNum] ) ),
    Table1[year]
)

Best Regards,

Herbert

[PBJ]

Many thanks for your input.

I tried out the formula on my data/table. The formula seems to indeed add values for the respective month, however does not add them to the previous ones. So, for July, it yields the sum of all values in July. For August, it yields all values in August, but does not include July ones.

[kcantor · Accepted solution]

@PBJ

You can do something like this YTD calculation. If you need it to cover different custom calendars, jsut add a custom calendar.

https://blog.crossjoin.co.uk/2009/12/01/first-steps-in-dax-year-to-date/

[KGrice]

To do cumulative sums, you'll need a column that can be recognized as a number, such as Month Number. In this case, I used StartOfMonth, so my table looked like this:

That column will allow you to write a measure where you get the sum of all of your Year column where the StartOfMonth is less than or equal to your current row's StartOfMonth. You can then create a new measure:

CumulativeYearAmount = CALCULATE(SUM(TableName[YearAmount]), FILTER(ALL(TableName[StartOfMonth]), TableName[StartOfMonth] <= MAX(TableName[StartOfMonth])))

 Drag StartOfMonth, Year, and CumulativeYearAmount onto a table in PowerBI and you should see this:

You could finally format the StartOfMonth column if you want it to look more like just the month name. The MMMM, yyyy option is the closest I could find.