Cumulative totals by month

[srhsloan]

Hi there,

 I've got some data like the following:

And I want to produce a graph that shows the following results:

So what this shows is the cumulative total at the end of each month of how many items existed, and at the end of that month a total of how many were active and closed.

The only way I can think to do it at the moment is to have a custom column for each month and then work out based on the active/closed dates what status it should be counted as for that month. Is there anything built in I can use instead?

I tried a clustered bar chart but that just showed me how many were created/activated/closed each month, rather than the cumulative total.

Thanks,

Sarah

[vanessafvg]

@srhsloan have you tried creating a running total from the quick measures?  

https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/

[srhsloan]

Thanks @vanessafvg I hadn't seen them. but I'm not sure cumulative total would work as some items will need to be subtracted. For example in my table above, in april, item 1 needs to be subtracted from the active total as well as added to the closed total.

[vanessafvg]

@srhsloan ok so to make it easier, what is the rule for each date can you tell me what the rule is i.e add or subtract?

[srhsloan]

It's based on what happend that month.

So Total of created for month Nx would be (Cumulative total of creatd for N0..N(x-1)) + (Total created for Nx). this one is simpler and I've just tried that quick measure and it works for this.

Total active for a given month Nx would be (Active Total Month N(x-1)) + (Items with activated date during month Nx) - (Items closed during month Nx)

Total closed is (Closed Total Month N(x-1)) + (Items closed during month Nx). I think the quick measure would probably work for this one too.

So It's just the active count that is  bit weird.