Goal: calculate tenure of any staff member as well as determining the average tenure of all current staff members at any given time.
I have a table with one row per day per employee based on their hiring (Date From) and termination dates (Date To). I planned on using RUNNING_SUM to calculate tenure. This works when looking at one employee but fails when multiple staff are included because the days worked by terminated staff are still included in the calculation. Another complication is some staff have multiple hire and termination dates. I do not want to count their length of service when they are no longer with the company but want it included again if they return.
The screenshot below shows what my data looks like for two staff members. The 'Total Staff' column fluctuates from 1 to 2 as one person is hired, leaves, returns, and leaves again. The blue highlighted columns show how I want the calculation to work.
I have attached a workbook with the data and would appreciate any thoughts/advice on how to achieve the desired output. Thanks in advance for your help!