Reply
derickyy
Frequent Visitor
Partially syndicated - Outbound

Cumulative Count to Specific Dates

Hi,

I want to build a rolling cumulative count column chart for the data below. The X-axis is the earliest Created date to now and the columns are the cumulative count of IDs to Finished date. For the rows which miss the Finished dates, count them from their Created dates to now. I also want to build two filters through Filter1 and Filter2 columns.

 

derickyy_0-1606866933605.png

 

Here is the code that I'm using currently:

CumCount= CALCULATE(COUNT('Table'[ID]),FILTER(ALLSELECTED('Table'),'Table'[Created] <= MAX('Table'[Created])))
 
I know this code can only yield the cumulative count of IDs to now, not to each ID's Finished date. Can you help me to modify my codes so that it can achieve the result above.
 
Really appreciate your time. 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@derickyy , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Also, refer to the HR blog is the current employee is one similar to what you need 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

Syndicated - Outbound

@derickyy , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Also, refer to the HR blog is the current employee is one similar to what you need 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

Hi Amitchandak,

 

Thank you so much for your reply. Here is the sample data. I'm not very familiar with sharing file on the community so I just stored it in my Google drive.

 

https://docs.google.com/spreadsheets/d/1k1Ugh95GUoqrQZU-5nTKnnFJYvbTF5dlnCoqEu1_iMY/edit?usp=sharing 

 

One more thing, with the Created date below, would it be possible to visualize the data in a weekly basis, instead of daily basis? If it can switch between them, that would be truly awesome.

 

Thank you so much for your time! 

 

 

Ashish_Mathur
Super User
Super User

Syndicated - Outbound

Hi,

Using the Query Editor, we can create 1 row for each date between the created and finished date therby doing away with 2 dates and replacing them with a single date column.  So the resultant dataset will have ID, Date, Filter1 and Filter2 columns.  From this dataset, we can build any chart you want.  Are you amenable to this approach? 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)