Cumulative 'net' totals based on dates in two columns

[Anonymous]

Hi there. this has driven me nuts the last couple of days....

I'm looking to build a measure that tracks the cumulative total but then drops the item once the end date has been reached. I have calculated the cumulative totals individually then built a third measure that subtracts the started items from those that have ended but the total doesn't meet the expected result (where the job is still open the dates are blank). Any ideas? 

1/ example of raw data

2/ I want to track the totals daily so have joined to date table

3/ example of measure:

Start_Cumulative = CALCULATE(COUNT('Date List START'[Plot Data.plotID]),FILTER(ALL('Date List'),'Date List'[d]<=MAX('Date List'[d])))

[Anonymous]

sorry to drag you back to this, @Anonymous, but I'm still struggling to implement your proposed solution - could you advise?

[Anonymous]

Hi @Anonymous ,

It's OK. You'd better provide me with some sample data(can be a simple example) and expected results, so that I can set up pbix to tell you the solution. I really can't do anything about your description. Sorry too.

Remember to hide private information.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

You can add a filter like

[End Date]>=TODAY()&&[End Date]=BLANK()

If you‘re still confused, please provide me with virtual data and corresponding expected results.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sorry Stephen, I'm not sure where or how to add this filter - The formula above doesn't let me add the end date into the filter.