cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

HR Analytics - Active Employee, Hire and Termination trend

In this article, we will cover the following HR Analytics calculations.

1. Active Employees: Current Employees
2. Hired Employees
3. Terminated/ Separated Employees
4. Last Period Active Employees: Last Period Employees
5. Period over Period Change %: Employee Change%

To achieve this, we have created an Employee Table. Also, we generated a Date table.

To get the best of the time intelligence function. We need to make sure we have a date calendar and it has been marked as the date in model view. Also, join it with the date column of fact/s. Refer to how to create.

This is the data we have:

And this is what the relationship diagram looks like.  Start Date joined with Date of Date Dimension and Active, in addition to Inactive relation Termination Date and Date.

We can also have both relations inactive.  There is an advantage to calculate Active employees if both are inactive.

Calculations:

``````Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )

Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Last Period Employee =
var _min_date = minx(all('Date'),'Date'[Date])
Return
CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=_Expression && Employee[Start Date]>=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Employee Change% = if(not(ISBLANK([Last Period Employee])),CALCULATE( (divide([Current Employees],[Last Period Employee]) -1)*100))``````

In case Start/Hire date join is active, you can use this:

``Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]) )``

No need for USERELATIONSHIP.

Also if both joins are inactive, you can use this:

``````Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date])
|| Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])))``````

No need for CROSSFILTER. We can also use count in that case in place of countx.

In the last period calculation, we have used ISFILTERED to check, which period we are using. Additional filter Employee[Start Date]>=_min_date is used to avoid going back in the past.

This how the trend looks like:

And Dashboard with new Ribbon and using new ribbon theme looks like this:

The pbix is attached to this article for you to explore.

There can be few more ways to get that. There can be better ways too. We are looking forward to hearing back from you on that.

Anonymous

@amitchandak I found the problem just after I sent my last message. My datasource was putting in a "1/1/1900" termination date for those employees that do not have a termination date.

I'm all set!

Thank you.

This solution solve my problem.  Thank you so much!!

@amitchandak Is it possible to sum the monthly values marked in yellow? So that the result can be shown in a year.

Dear @amitchandak ,

Thank you for a great post. If this works, it would really solve my problems. 🙂

I tried to replicate your sheets (with my own values), but I do not get the 'Hired Employees' and 'Terminated Employees' match with the 'Year Month'.

Any idea what I can have missed?

Where would you first fault trace?

Top Kudoed Posts
Latest Articles
Archives