Cumulative Count to Specific Dates

[derickyy]

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.

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. 

[amitchandak · Accepted solution]

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

[derickyy]

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]

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?