cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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])))

Thank you so much in advance!

1 ACCEPTED SOLUTION
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])))

3 REPLIES 3
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 .

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:

Not quite there yet.

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])))

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors