Transforming Employee Roster into a Headcount Calendar

[Michaperki]

Hello, I've started a new job recently and one of my first tasks as a BI analyst is to create a regularly updating dashboard showing employee headcount over time.

The input dataset is a roster export from our HR system. The data includes the following columns:

• Employee Name
  
• Function
  
• Start Date
  
• Termination Date (if the employee has left / will leave on a certain date).
  

The output should be a calendar view showing the total headcount for each function by month.
Really just three columns:

• Month
  
• Function
  
• Headcount
  

I've attached an Excel file with sample input and the desired output (apologies, but I wrote this manually and overlooked one software developer so there is a small error in the output).

I have found a workable solution but came here to refine my approach and collect ideas.
Current approach:

1. Looking at just 'Start_Date', add 12 Boolean 'Is_date_prior_to' columns for each month in 2022 (Is_date_prior_to_Jan, Is_date_prior_to_Feb, etc...)
   
2. Perform 12 group by operations (one on each of the new columns) to create 12 new dataframes showing monthly onboarded headcount. Append these dataframes to create Start_Headcount_DF
   
3. Repeat steps one and two for 'End_Date' to create End_Headcount_DF.
   
4. Left Join the new End_Headcount_DF onto the Start_Headcount_DF.
   
5. Subtract the End_Headcounts from the Start_Headcounts
   

If helpful, I can prepare/share a script showing my current process. But I am hoping that this description of the task is enough detail for you to propose an approach.

Thank you for your help!
Michael