Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@srhsloan have you tried creating a running total from the quick measures?
https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/
Proud to be a Super User!
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.
@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?
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |