Forum Discussion
Cumulative Amounts
- 10 years ago
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/
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.