visualization Archives

visualization

Calculating Employee Turnover in DAX- In and Out

September 21, 2021

Employee turnover is a term that is widely used in HR analysis. Even though I am not an HR Professional, I know that people define Employee turnover differently.

And I’m going to go with the formula that is frequently found on the internet. (I’m pretty sure that if you learn this one, any other turnover formula will be easier to calculate using DAX 😉)

To start your employee turnover calculation, you should divide the total number of employees left during the given period by your average number of employees in the given period. Then, times the total by 100.

This formula seems pretty easy, right? Even so, calculating this in Power BI is a bit tricky.

Now roll up your sleeves because we are gonna do some serious DAX!😎

The only data we have with us to calculate the employee turnover are Employee ID/Name, Date of Appointment, and Date of Resignation (Whatever you call it in your organization)

To make everything easier, I went step by step breaking down the numerator and the denominator until the only pieces that left are the above data that I have.

I’ll explain using the DAX code itself.

Creating a Date Table

The most important thing that you should have first is the Date Table. Believe me! It is a lifesaver! ❤️ You can simply use the CALENDARAUTO() function to create a Date Table. (There are some other functions too if you want to try them out.)

You’ll need the date table to refer to Employee start date(Date of Appointment) and Employee end date(Date of resignation).https://www.linkedin.com/embeds/publishingEmbed.html?articleId=7641001261280701347

So I am using an Employee Table which consists of Employee ID, Employee Name, Start Date, End Date, and other HR-related data.

Now let’s discuss how we can calculate turnover per month. You can tweak the code a bit to make it annual.

Make sure to add a column that calculates the Month and Year.

Defining Variables

First things first, defining the variables! Put it simply, you need two main variables which you will be used to create other variables.

Month Start Date: Calculates the first day of the month

var MonthStartDate = STARTOFMONTH(‘Date'[Date])

Month End Date: Calculates the last day of the month

var MonthEndDate = ENDOFMONTH(‘Date'[Date])

Calculating Number of Employees Left During the Month

It’s time to get your hands dirty!

Now that you have Month Start Date and Month End Date in your hands, use the following code to calculate the number of employees during the month.

💡 You can use Start Date instead of End Date to calculate the number of employees joined during the month. (If you need it in another measure)

Calculating Average Employees in the Month

Here’s the tricky part!

You know that Average Employees in a period is calculated by dividing the sum of Employees as at the beginning of the month and Employees as at the end of the month by 2.

So I calculated these two separately.

Let’s talk about how we can calculate the number of employees at the beginning of the month. And you’ll be able to figure out the second by yourself.

Here’s what I did. I calculated ALL the employees joined until the beginning of the month. Then ALL the employees left until the beginning of the month. And got the difference!