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 🙂
kameronyork Below is the date measure I am using. Max('Lead Times'[LeadTime]) is the number of days I am using from another table which has a relationship with current table
Would you please post more information about this lead times table? Why does the date need to be pulled from it? This will hlep me understand the question better 🙂