I have 2 separate HR reports that I am running through Power Query. These reports are updated monthly, replacing the previous month.
Report 1 (Pivot table 1): Shows all current active employee
Report 2 (Pivot table 2): Shows all terminated employees historically
I am working on a dashboard to calculate historical turnover for the HR department and have hit a road block.
I have figured out that in order for me to calculate the total employees for a specified period in the past I would need to filter Report 1 by "hire date" for current employees up until the specified date needed for turnover period.
I then would need to filter Report 2 for only the specified month to get the total terminations for that month.
Then add all of the terminations from Report 2 to the current employees from Report 1 in order to get than actual amount of employees who were employed at that time.
Afterwards the formula would be (terminations from report 2 / total from report 1 and 2) to calculate turnover.
Is there a way that this can be done for the dashboard without manually having to go through this process?
Hi Shawn,
Are you able to merge the two reports into one table? Presumably there is a 'terminated date' column and any active employees will have a blank in that column and any terminated employees will have a date. This will enable you to filter the data accordingly.
Check out this Excel HR Dashboard tutorial for ideas on how to calculate active vs terminated employees etc.
Mynda
Thank you for the reply. I was able to merge the reports, but am having trouble actually seeing the total number of employees at a filtered time period. Basically, the Active employees report would need to be filtered back to a specific date and then the Terminated report employees for that filtered time period would need to be added into the Active. I am really only able to see one or the other in the pivot table. Would a measure be needed to complete this calculation?
Yes, you'd have to write a measure that checks if the current date (filtered or row label in the PivotTable) is within the employee's start and termination dates, if so, include them in the count.
So I have messed around with this issue more and have realized that in comparison with your HR dashboard video. The file structures are different due to the count only wanting to count an employee ID once by hire date and then not add it back to the subsequent months. Is there a way to get around this issue?
This is an example of the data.
Hi Shawn,
Looks like each company is using a different way to calculate turnover.
Here is one of them:
In the attached file, I added 2 measures to calculate your Reports 1 and 2, but have a hard time understanding what you mean by:
Then add all of the terminations from Report 2 to the current employees from Report 1 in order to get than actual amount of employees who were employed at that time.
Let me know please if the calculations attached are correct.
