Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
charlie_torres
Frequent Visitor

Cumulative count by category and date

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

 

charlie_torres_0-1674729168800.png

 

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

 

charlie_torres_1-1674729263618.png

 

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!

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

 

View solution in original post

3 REPLIES 3
charlie_torres
Frequent Visitor

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
Frequent Visitor

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:

charlie_torres_0-1674814986237.png

 

Not quite there yet.

 

amitchandak
Super User
Super User

@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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.