Forum Discussion
How to cumulative sum with condition
- 3 years ago
This can be accomplished either in a measure to be used in a table, or in a calculated column in PowerBI's Data View. The measure, when applied to a table - with the Date column added - will result in this:
The column added in the Data View will result in this:Here is the code I used:
MeasureCumulative Sum = VAR current_date = SELECTEDVALUE('Table'[Date]) RETURN CALCULATE( SUM('Table'[Forecast Value]), 'Table'[Date] <= current_date) // Returns the sum for all dates less than or equal to the date on the current row.Column
Running total = VAR current_date = 'Table'[Date] RETURN CALCULATE( SUM('Table'[Forecast Value]), ALL('Table'), 'Table'[Date] <= current_date) // Returns the sum for all dates less than or equal to the date on the current row.Also, If you wanted it to count up differently based on certain criteria - like when it becomes a new year - I would suggest using the column method and doing something like the following:
I hope this helps! If it does, please mark this as the solution. Kudos are appreciated 🙂
This can be accomplished either in a measure to be used in a table, or in a calculated column in PowerBI's Data View. The measure, when applied to a table - with the Date column added - will result in this:
The column added in the Data View will result in this:
Here is the code I used:
Measure
Cumulative Sum =
VAR current_date = SELECTEDVALUE('Table'[Date])
RETURN
CALCULATE( SUM('Table'[Forecast Value]), 'Table'[Date] <= current_date)
// Returns the sum for all dates less than or equal to the date on the current row.Column
Running total =
VAR current_date = 'Table'[Date]
RETURN
CALCULATE( SUM('Table'[Forecast Value]), ALL('Table'), 'Table'[Date] <= current_date)
// Returns the sum for all dates less than or equal to the date on the current row.
Also, If you wanted it to count up differently based on certain criteria - like when it becomes a new year - I would suggest using the column method and doing something like the following:
I hope this helps! If it does, please mark this as the solution. Kudos are appreciated 🙂