Forum Discussion
Help with cumulative counts based on category
I'm not even sure if I'm using the right terminology here, but I need some assistance. I have a table that looks like this:
| ProjectId | RoadmapId | Status | ChangedDate |
| 599 | 9 | Not Started | 26/08/2016 |
| 599 | 9 | Hold | 18/09/2018 |
| 599 | 9 | Research | 19/08/2019 |
| 599 | 9 | Hold | 08/07/2020 |
| 628 | 4 | Requirements | 26/11/2015 |
| 628 | 4 | QA | 19/07/2016 |
| 628 | 4 | Implementation | 03/11/2016 |
| 628 | 4 | Completed | 30/12/2016 |
| 631 | 9 | New | 17/11/2015 |
| 631 | 9 | Cancelled | 28/10/2016 |
| 633 | 9 | New | 17/11/2015 |
| 633 | 9 | Not Started | 02/08/2016 |
| 633 | 9 | Cancelled | 17/11/2017 |
I need to show a table in Power BI that counts the cumulative status of each RoadmapId as of the Year/Month. For instance as of Aug 2020, how many projects are on hold for RoadmapId 9. When it was July 2020, how many were on hold at that time, etc.
| RoadmapId | 9 | 4 |
| Feb | 1 | |
| Mar | 4 | |
| Apr | 1 | 3 |
| May | 6 | 1 |
| Jun | 1 |
4 Replies
RobG - Not entirely sure I understand exactly what you are going for. I do not understand how you get to your output from the sample data provided.
Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.RobG , with a date table try like
Cumm = CALCULATE(count(Table[RoadmapId]),filter(date,date[date] <=maxx(date,date[date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Hi RobG ,
Is the answers above helpful? If yes, please accept them as solution. If you still need help, can you please tell us how to calculate it? I'm not clear about your sample data and the expected output. Can you please explain it fully?
