HR Analytics - Active Employee, Hire and Termination trend

[amitchandak]

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])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',DATEADD('Date'[Date],-1,MONTH)),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
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.

Current_employee.pbix

[nhoward]

Thank is a clever way to do that.  

I have done it by treating Starters and Enders as Facts.  My HR Model has leave transactions (accruals and takens), pay transactions, Starters and Edners as types of Facts all in one big table.  My measures then filter on the Fact Type column (within a calculate formula). 

BeginningActives = CALCULATE(DISTINCTCOUNT(PayEvents[CoyEmpCode]), DATESBETWEEN(Calendar1[DATE], STARTOFYEAR(Calendar1[DATE],"30-Jun"), Max(Calendar1[DATE]))) - CALCULATE(counta(PayEvents[CoyEmpCode]),PayEvents[TranType] IN {"Starter"}, DATESBETWEEN(Calendar1[DATE], STARTOFYEAR(Calendar1[DATE],"30-Jun"), Max(Calendar1[DATE])))

Enders MTD = CALCULATE(TOTALMTD(counta(PayEvents[CoyEmpCode]),Calendar1[DATE]), PayEvents[TranType] IN {"Ender"})

[amitchandak]

@nhoward, thanks for sharing your experience.

[Anonymous]

Hi there,

How do you address the re-hired employees?

Thanks,

Oded Dror

[nhoward]

Hi Oded,

If the same record is used (ie term date removed) there is little that you can do.   I recommend starting a new employee record when re-employing a 'properly' terminated employee.   Some systems have a copy function so at least you don't have to re-type all there details. 

[FrankTonsen]

I'm not an HR guy. Is this correct?

March-2017   Current Employees 10   Last Period Employee 9

April-2017     Current Employees 10   Last Period Employee 11

Otherwise, for instance

Last Period Employee Adjusted =

IF(ISFILTERED('Date'[Month Year]); CALCULATE([Current Employees]; DATEADD('Date'[Date];-1;MONTH)); CALCULATE([Current Employees]; DATEADD('Date'[Date];-1;YEAR)))

[amitchandak]

@FrankTonsen, Good Catch. It was because of End of Month was not considered.

The corrected formula is

Last Period Employee = 
var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
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))

[Anonymous]

Thank you all

Oded Dror

[Anonymous]

How do you write the dax query for "last period employee" if you have no active table relationships to the date table? @amitchandak @FrankTonsen 

[FrankTonsen]

@Anonymous,

what do you mean?

- no date table -> create one

- no relationship to the date table -> create one

- no active relationship to the date table -> activate one (USERELATIONSHIP function)

[Anonymous]

Hi AC,

I have been using a very similar setup than yours and was trying to calculate Last Period Employees. 
The outcome however gives me a sum of all previous New Hires in that year, not a comparison of current employees to last period. 

Example: I filter on March 2019, the value it gives me is the summed up total of New Hires January and February 2019. What it should give me is the the current employee numbers of February 2019.

Here is my measure setup:

Last Period Employee =

var _min_date = minx(all('Calendar');'Calendar'[Date])

var _Expression=if(ISFILTERED('Calendar'[Month_Year]);maxx('Calendar';ENDOFMONTH(DATEADD('Calendar'[Date];-1;MONTH)));maxx('Calendar';DATEADD('Calendar'[Date];-1;YEAR)))

Return

CALCULATE(COUNTx(FILTER('Mitarbeiter';'Mitarbeiter'[Start Datum]<=_Expression && 'Mitarbeiter'[Start Datum]>=_min_date && (ISBLANK('Mitarbeiter'[Wirksam zum]) || 'Mitarbeiter'[Wirksam zum]>_Expression));('Mitarbeiter'[xrm1_employeeid]));CROSSFILTER('Mitarbeiter'[Start Datum];'Calendar'[Date];None))

The data structure is identical to yours, except for some naming. I have an active and inactive relation between employee and calader table, with Start Date ("Start Datum") being active and End Date ("wirksam zum") being inactive. I can't figure our why the calculation would deviate to summing up new hires instead of returning current employee count for last period. 

[Sgeater]

Hi, how come if you put [Terminated Employees] into a stacked column chart and have a table with [Name], when you select a month from the column chart it returns the name of the person who started that month.

eg. March -2019 shows 1 terminated employee & when click March 2019 on the column chart it filters to show R left that month, but actually R doesn't have an end date but DOES have a start date of March 2019. Other months return no names.

I assume it's to do with the active relationship between the tables and the start date. But unsure how to adjust it to use as I intend.

I would like to use what you have put together as its very good. But be able to use it as a slicer, by putting [Terminated Emloyees] & [MonthYear] onto a column chart, then when selecting a month with terminated employees be able to have those names show on a table with the [Name].

Thank you.

[frankvb]

@amitchandak 

Could I ask what is the purpose of doing the following piece of DAX when calculating the current employees?

Employee[Start Date]<=max('Date'[Date])

Here is the rest of that measure for context:

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))

I just can't grasp why you would do this.

Thanks in advance!

[nhoward]

@frankvb

That filter will limit the list of employees to those with Start dates before the last date in the date table (as filtered).  When someone select Nov 20 as the month, we don't want employees who started after 30/11/20 to be included. 

[frankvb]

Is it possible to use this solution, when you have separate date dimensions for the end and the start date? I'm working on a model in a Analysis Services live connection, and I can't implement a right now.

[grggmrtn]

@amitchandak- Thanks so much for this, it's almost exactly what I was looking for 🙂

My problem is with "Current employees". Our data is organized in such a way that the same employee can be included several times in the list (think of it as employees that can have connections to several different departments in the same firm, so that they appear several times).

So, is there any way to modify "Current employees" so that it returns a uniqe count instead of counting the same employee several times?