Help creating employee turnover report - new to power pivot

[karenmi]

I am working with a data file from our HR system, and need to create a turnover report that shows percentage turnover each month, by property, by department, and by a bunch of other slicers that I create. I am comfortable with Power Query and merging tables to get all of the data I need for the slicers. I need help framing the power pivot measures (or however I should be approaching this) in order to calculate the % turnover each month.

My data includes all employees who have ever worked for the company. I filter out for employees terminated prior to this year. The person currently creating this report somewhat manually is calculating turnover as # of employees terminated this month divided by the average of the # of employees at the beginning and ending of last month (employees on the 1st + employees on the 30th divided by 2). Some employees are terminated and then re-hired. The file looks like this:

If anyone can help me figure out how to structure the turnover calculations I would greatly appreciate it!

 

[Matt Allington]

This is a non-trivial task. You need to iterate through every employee in the period selected (say a year) and work out how many new starts and how many terminations.

I wrote this article a few weeks ago. It is not the answer you need, but it may help you understand what needs to be done.

How Many Employees by Period

I have many reasons for sharing content online. The main reasons are that I like sharing what I know, but also that I want to document how to complete a task for future reference. My article today ticks both of those boxes. How Many Employees by Period I’m calling this [...]Read More »

exceleratorbi.com.au

I’m my example, instead of counting rows in the employee table, I would use that table as the input for an iteration, and then check if each employee was either started or terminated in the period. A further complication is they may have done both, even multiple times.

Like I said, not trivial, particularly if you are new to it all. I don’t have capacity to solve this one for you for free, but if you want commercial help, I can get Jason to help you for 1 hour of his time. Remote Help/Support - Senior Consultant (Jason ****ington)

Otherwise, I hope this information helps you on your journey.

 

[KarenXL]

Thanks so much Matt. I will give it a go and if I am not successful I will get in touch with Jason. I appreciate your help and input.