cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srhsloan Frequent Visitor
Frequent Visitor

Cumulative totals by month

Hi there,

 

 I've got some data like the following:

 

image.png

 

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

image.png

 

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

4 REPLIES 4
vanessafvg Super Contributor
Super Contributor

Re: Cumulative totals by month

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

 

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
srhsloan Frequent Visitor
Frequent Visitor

Re: Cumulative totals by month

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 Super Contributor
Super Contributor

Re: Cumulative totals by month

@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?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
srhsloan Frequent Visitor
Frequent Visitor

Re: Cumulative totals by month

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.

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 256 members 2,819 guests
Please welcome our newest community members: