Employee Turnover by month

[ADN75]

Hi all,

I'm trying to have the turnover of employee by month (number of arrival - number of departure).

I have two columns : Arrival date and Departure date (note that this column is empty if the employee is still here).

I want to create a measure to have the difference between number of arrival and number of departure.

Exemple :

Employee	Arrival Date	Departure Date
A	2021/01/01
B	2021/02/01	2021/05/01
C	2021/03/01	2021/07/01

My measure should get me : 

- January : 1

- February : 1

- March : 1

- April : 0

- May : -1

- June : 0

- July : -1

Can someone help me?

Thank you in advance

[Ahammedjaleel]

Please find same kind of report here

https://www.linkedin.com/posts/ahammed-jaleel-33772b5b_employee-performance-and-attrition-analysis-a...

the dax I used for turnover ration is as follows

Employee Turnover Ratio =

Var _Selectedvalue = [SelectedYear]

var _Lastyearcount = CALCULATE(COUNT(HRDataset_Final[EmpID]),HRDataset_Final[DateofHire - Year]<=_Selectedvalue-1)

var _CurrentYearCount = CALCULATE(COUNT(HRDataset_Final[EmpID]),HRDataset_Final[DateofHire - Year] <=_Selectedvalue)

var _ResignedEmployee = CALCULATE([Employees Terminated],FILTER(HRDataset_Final,HRDataset_Final [DateofHire - Year]=_Selectedvalue))

var _Avg= DIVIDE(_Lastyearcount+_CurrentYearCount,2,0)

RETURN

DIVIDE(_ResignedEmployee,_Avg,0)

let me know if you need any more  details

[selimovd · Accepted solution]

Hey @ADN75 ,

you should create a date table and connect it with one of the dates, for example Arrival Date. Then you can use the month from the date table and just count the rows of arrival to get the first measure.

For the second measure (departure), you have to change the relationship in the measure with the USERELATIONSHIP function, count the rows and mulitply by -1.

Then you should have the result you want.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[ADN75]

It works, thank for your help !