Cumulative count by category and date

[charlie_torres]

Hi Everyone, I have been scrolling for a few days, but I must turn it in here as I need a solution to match my search. I have a few columns I unpivoted to obtain a single column of the different categories I wanted to report. These categories are milestones that will be achieved throughout dated periods. I want to create a cumulative count by category that groups the counts of those milestones by category occurring in the same month. The picture reflects the source data from which I want to achieve this.

🔺[Milestones Label]: Column I want to report separately 

🔹[Milestones Forecast]: Real date of occurrence

💚[Milestones Int]: all milestones are formatted to the 1st of each month. Thought to be needed for monthly cumulative count. 

💜[Milestones_Month_Year]: The column use as table headers to report the cumulative count by category

◼️[Milestones Count Live Building Schedules]: Cumulative counts values

Here is the excel table with the milestones cumulative count by date I am trying to achieve in powerbi:

Here the formula among others I have tried so far with no success

Milestones Count Live Building Schedules = CALCULATE(COUNTA('140 Milestones Draft'[Milestone Label]), FILTER('140 Milestones Draft','140 Milestones Draft'[Milestone_Month_Year]<=EARLIER('140 Milestones Draft'[Milestone_Month_Year])&&'140 Milestones Draft'[Milestone_Int]<=EARLIER('140 Milestones Draft'[Milestone_Int])))

@PBICommunity 

Thank you so much in advance!

[charlie_torres]

Your suggestion was the right solution. I just realized there were missing dates, which is why the figures didn't match on the first try. It is all sorted now. Thank you so much @amitchandak .

[charlie_torres]

Hi @amitchandak

This was of great help. I checked both videos, and following this guidance, I am the closest I have ever been to achieving this. However, the examples here mainly focus on adding cumulative sales values. In my case, I am looking to add a cumulative by-date of milestones by category, meaning that I need to add labels sharing the exact date. Therefore the count increments by date but differs among labels. So far, I have come out with this formula: 

Rolling 12 = CALCULATE(COUNT('140 Milestones Draft'[Milestone Label]), WINDOW(0, ABS, 0, REL, ALLSELECTED( '140 Milestones Draft'[Milestone Label], '140 Milestones Draft'[Milestone_Int]), ORDERBY('140 Milestones Draft'[Milestone_Int])))

Result:

Not quite there yet.

[amitchandak · Accepted solution]

@charlie_torres , Try to use window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

You can also use date table and measure like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41