Forum Discussion
Running total calculation
Hello,
I need to create a cumulative total for year to year adding new projects that have started, but also subtracting projects that have ended based on two columns. I have read the article on cumulative total and have a seperate date table, but am not sure if there is a measure I can create that also subtracts projects that have already ended. Any help would be greatly appreciated.
Thank you.
Hi Elisa_E,
Instead of using running total calculations could you use a measure like this to get the number of active projects in a year.
ActiveCount = var temp=MAX(Table1[Start]) return CALCULATE(COUNT(Table1[ProjectID]),Table1[Start]<=temp, Table1[End]>=temp)
You might want to change the Table1[End]>=temp to just >temp depending on your business logic.
That producese the following based on the example data
2 Replies
Hi Elisa_E,
Instead of using running total calculations could you use a measure like this to get the number of active projects in a year.
ActiveCount = var temp=MAX(Table1[Start]) return CALCULATE(COUNT(Table1[ProjectID]),Table1[Start]<=temp, Table1[End]>=temp)
You might want to change the Table1[End]>=temp to just >temp depending on your business logic.
That producese the following based on the example data
This works perfectly! Many thanks!!
