Employee turnover

[Anonymous]

Hello,

I want to calculate the number of employees starting and leaving each month.

Employees can start new at the company or starting at another region, team or unit within a company, same for leaving.

A unit is part of a team, a team is part of a region and a region is a part of the company.

Employees can start or leave at one or several units at the same time, same for leaving.

I have a table with a row per employee, day and unit. The employee doesn't excist on a day if there is no active employment.

Attatched a example of the table I have got.

I think the solution must be something like searching for a row per employee, per unit, per day one day ealier or later. When this doesn't exists there must be a count.

I hope the example works and the question is clear.

Luuk

[v-xicai]

Hi @Anonymous ,

I am not sure what desired result would you want, could you please share your desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy

[Anonymous]

Hi @v-xicai 

Thank you for your answer.

I added a tab with the desired output.

For example employee 9236064:

Exists for the first time on 2019-04-20, so starting at Company, Region, Team and Unit in the month april.

On may 31th this employee moves to another Unit and Team but within the same Region. So in may this employee will be counted as a leaver for the Team 20030 and Unit 23803. This employee will be counted as a starter in june for the Team 20310 and Unit 20044. On Region and Company level there are no changes for this employee. Exists for the last time on 2019-08-10, so starting at Company, Region, Team and Unit in the month april.

Probably I am searching for an DAX query which counts on Unit, Employee and Date level whether a row exists a day earlier or later.

Luuk