How to calculate employee headcount by department over a period of time with transfers
I have managed to calculate the headcount and actual number of staff by department over a period of time using starting date and leaving date using a calendar table. However, now i need to re-do the analysis which should also take into consideration the headcount of employees that have transfered to another department. Headcount will be the aggregate of new joiners Active Staff will be the Head count - leavers - transfers Lets say the table is Employees and has the following fields: a) Employee ID b) Date of joining c) Departmend joined d) Transfer date e) Department transfered to f) Leaving Date If employee A joined in January 2022, Department Finance, then the headcount of Department Finance should be incremented by 1 and active staff value should be incremented; If the employee A transfers to another department (Procurement) in August 2022 then the headcount of Department Finance should not be impact but the active staff should decreased by 1 and the headcount and active staff of department Procurement should be incremented by 1. In summary, need to monitor the headcount and active staff by department as changes to joiners and transfers and leavers happen over a period of time. Any help would be really appreciated.