cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
derickyy
Frequent Visitor

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 IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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 III
Super User III

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/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.