Help with cumulative counts based on category

[RobG]

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

[v-xuding-msft]

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?

[amitchandak]

@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-Y...


Appreciate your Kudos.

[parry2k]

@RobG check this post if helps

[Greg_Deckler]

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