Employee turnover

[Anonymous]

Hello,

I am tasked with making a calculation of what percentage of people has left the company group for each period using the PowerBI. The link to the mock data is as follows: https://docs.google.com/spreadsheets/d/1ZsPW3WywsKEvOK7mcY0MUcXG7nC_kjtcEF1cnPtkkOo/edit?usp=sharing. The HR has agreed to use the following calculation:

Employee turnover = (number of employees leaving the company / ((Total employees at the beginning of the period + Total employees at the end of the period) / 2)) * 100%

While it may seem easy, apparently it got me stuck because of several problems:

1. Some employees left the company. Some did not actually leave, they just moved to another company within the same group. But the leave date is recorded for administration purposes.
2. Some employees left the company only to come back after some time has passed.

I believe this creates duplicates which I need to address by making a separate table with only the Employee Code and Employee Name which then I must link to the data table. However, they want to count employee on problem #2 as 2 separate counts.

What they want to see:

1. Percentage of employee turnover.
2. Number of employees leaving each company.
3. They need to be filter it by year and company.
4. Names of people who has left the company.
   
   I tried following this youtube tutorial for calculating the Total employees at the beginning & end of the period: https://www.youtube.com/watch?v=7N_NkVQreBQ. Also, I noticed that while he said that the starting headcount will always go down as time goes on, that is not the case with my calculation. Additionally, the ending headcount in my case is not always the same with starting headcount, since there are people who left at the last day of the year, so the starting headcount on the next period is less than the end headcount at the end of the year.
   
   Can you help me please? I am still new to PowerBI, please be kind. Thank you in advance.

[Poojara_D12]

Hi @Anonymous 

I have tried to give you the DAX:

Total_Employees_Start = 
CALCULATE(
    COUNTROWS(EmployeeTable),
    FILTER(EmployeeTable, EmployeeTable[StartDate] <= MIN(DateTable[Date]))
)

Total_Employees_End = 
CALCULATE(
    COUNTROWS(EmployeeTable),
    FILTER(EmployeeTable, EmployeeTable[StartDate] <= MAX(DateTable[Date]))
)

Employees_Leaving = 
CALCULATE(
    COUNTROWS(LeaveEventTable),
    FILTER(LeaveEventTable, LeaveEventTable[LeaveDate] >= MIN(DateTable[Date]) && LeaveEventTable[LeaveDate] <= MAX(DateTable[Date]))
)

Turnover_Percentage = 
DIVIDE(
    [Employees_Leaving],
    DIVIDE([Total_Employees_Start] + [Total_Employees_End], 2)
) * 100

• Create a unique Employee table for tracking individuals.
• Use measures for counting employees at the beginning and end of the period.
• Track the number of employees leaving during the period.
• Use these values to calculate the turnover percentage and filter by company and year.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Anonymous]

Hi @Poojara_D12 ,

Thank you for the prompt response.

Could you please elaborate what do you mean by LeaveEvent table?
Additionally, the formula countrows counts duplicates which i have (as shown on the mock data). I assume you mean distinctcount() instead?

Regards,