top of page

H.R. Analytics — Employee Retention

Human resources (H.R.) analytics is collecting, measuring, and analyzing data about an organization’s human capital to improve H.R. decision-making and optimize H.R. practices. H.R. analytics can help organizations make data-driven decisions about their workforces, such as identifying team member turnover, predicting the success of potential hires, and team member development programs.

There are several key areas where H.R. analytics can be instrumental.

  1. Talent acquisition: H.R. analytics can help organizations identify the most effective sources of job candidates and the characteristics of successful hires. This can help organizations improve their recruiting efforts and reduce the cost and time associated with filling open positions.

  2. Performance management: H.R. analytics can be used to assess team member performance and identify areas for improvement. Organizations can develop targeted interventions to support team member development and increase overall performance by analyzing productivity, attendance, and engagement data.

  3. Employee retention: H.R. analytics can help organizations understand why employees leave and identify trends in voluntary turnover. This can inform retention strategies and help organizations reduce the costs associated with high team member turnover.

  4. Workforce planning: H.R. analytics can be used to forecast future workforce needs and identify potential skills shortages. This can help organizations plan for future workforce needs and ensure they have the right mix of skills and talent to meet business objectives.

There are several tools and techniques that organizations can use to support H.R. analytics. These include data visualization tools, such as dashboards and graphs, and statistical analysis techniques, such as regression analysis and machine learning algorithms.

Organizations must ensure they have the correct data and infrastructure to implement H.R. analytics effectively. This includes having a centralized H.R. system that captures data on team member performance, development, and engagement and the ability to integrate data from other sources, such as payroll and time and attendance systems.

Overall, HR analytics can be a powerful tool for organizations looking to optimize their H.R. practices and make data-driven decisions about their workforce. By collecting, measuring, and analyzing data on team member performance, development, and engagement, organizations can identify trends, predict outcomes, and make informed decisions that drive business results.

We will focus on understanding employee retention/turnover, i.e. the rate at which the employee stays/leaves the organization.

You already know my favourite language. So let’s do this with DAX.

The availability of data is as crucial as analytics itself. What variables in your data are needed to understand who leaves the company and who stays? Take a quick guess.

You need three essentials variables, namely

  1. Employee number or name

  2. Employee start date

  3. Employee end date

You can download the data here. The dilemma with data like this is you have to work with multiple date columns meaning just creating an active relationship between your date table and the employing starting date isn’t sufficient. Let’s see why this won’t work.

For the dax above, we are saying if there is no end date for an employee, the employee is still with the organization. The output is still as seen below.

The output is iterating through each date context and then calculating the no of employee that started on that particular date which is not what we want.

How do we overcome this dilemma? We must find a way to iterate between the start and end dates.

The solution is to create an inactive relationship between the date table and the start and end dates.

Current Staff

The DAX that iterates between the two dates and then calculates the number of current staff in an organization within a timeframe is seen below.

Let’s go behind the scene to see how it runs.

The date column on the date table provides context to the dax measure. The first variable, start_date_filter, means that we filter the employee table only to return employees whose starting date is less than the maximum date. The max and min dates are equal for a date context, as seen in the image below. For example, on 14/1/2018, the start_date_filter will return a list of employee who started on or before that date.

The 2nd part of the measure is the end_date_filter.

There are two conditions to determine if an employee is still a part of the organization.

  1. The end date is blank.

  2. The end date hasn’t been exceeded based on the current date context.

The end_date_filter is getting a list of employee whose end date is greater than or equal to the minimum date (a date in the future) or whose end date is blank. The equality condition is because if today is supposed to be my last day in an organization, I’m still a member of that company for that day. Let us better understand how this works with an example.

For a date context, and using the date 10/2/2018. The end_date_filter will return a list of employees whose end date is blank or either 10/2/2018 or after, meaning on February 10; employees on the list are still members of the organization.

The last part of the measure, the CALCULATE, returns the no of employee based on the two filters applied.

The DAX measure works in multiple ways based on the context applied. The numerous ways result from the change in Min and Max Dates. So let’s go through the three different contexts.

  1. Date context

  2. Month context

  3. Year context

Date Context

We can see the min and max dates are the same for every row. We are using 10/2/2018 as an example. The measure counts the number of employees who started on or before 10/2/2018 and whose end date is on or after 10/2/2018 as the no. of current staff.

Month Context

For a month context, the max and min dates are different. The min date is the 1st day of the month, and the max date is the last day of the month. Using Feb 2018 as an example, the measure returns the number of staff who started on or before 28/2/2018 and whose end date is either blank or greater than or equal to 01/02/2018.

Here I’m assuming that even if an employee leaves during the middle of the month, that person should be included as current staff for that month.

Year Context

This works similarly to the Month Context. Here, the max and min date is the last and first day of the year, meaning even if a person leaves on the 1st day of the year or during the year, count that person as an employee for that year.

Staff Turnover

This speaks to the number of employee leaving the organization. How do we calculate this with DAX?

See below

The DAX measure works similarly to the current staff measure, i.e. count the number of staff whose end date is not blank and less than the minimum. Recall that the min date is based on the context. For month context, we are saying if the employee’s end date is neither blank nor greater than/equal to the 1st date of the month, the employee should be regarded as having left the organization for that month.

We can then plot the measure.

We can see how we just turned a bunch of dates into something concrete. We are already seeing an increasing pattern of staff leaving the organization, which begs the question of why they are leaving. Is it voluntary? If so, do we, as an organization, get feedback on why they are going? If No, we then begin to see a flaw in the H.R. process because if people leave for particular reasons and we have zero ideas of the Why. How, then, do we tackle the problem to ensure other members don’t go for the same reasons in the future?

Data is not just about revenue and cutting costs. If channelled right, you can use data to improve the overall business.

As this is the 1st article of the year. I wish all my readers a Happy New Year. I wish you the best 2023 has to offer.


bottom of page