cancel
Showing results for 
Search instead for 
Did you mean: 
amitchandak

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:
Screenshot 2019-12-22 12.37.51.pngScreenshot 2019-12-22 12.37.58.png

 

 

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.

Screenshot 2019-12-22 12.36.54.pngScreenshot 2019-12-22 12.37.29.pngScreenshot 2019-12-22 12.37.33.png

 

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

Screenshot 2019-12-22 12.37.05.png

 

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:

Screenshot 2019-12-23 20.29.49.png

 

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

Screenshot 2019-12-23 21.33.46.png

 

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.

 

 

Comments

@amitchandak  thanks for fast respond. 

 

I would like to calculate number of active cases in month, it can be average, so opened on of before choosen date and resolved or closed after choosen date.

 

 

I can't get this to work. 
I want to count number of projects added, started, and delivered within a certain time frame that the user can change (Count projects by month, quarter, year etc). I've made a date table and established an inactive connection from the Date field to all 3 fields in my original table. 

Sample Data

Project NameIDCreatedStart DateDelivered Date
Account Analysis18/1/20228/15/202211/13/2022
Sales Analysis29/26/202210/1/2022 
Forecast Analysis310/4/202210/10/202211/1/2022
Report Clean Up411/22/2022  
Customer Dashboard57/8/20227/21/2022 


DAX used to make date table: 

Calendar =
VAR _calendar =
    CALENDARAUTO()
RETURN
    ADDCOLUMNS(
        _calendar,
        "Year", YEAR ( [Date] ),
        "MonthNumber", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "mmmm"),
        "Quarter", "QTR " & FORMAT( [Date], "Q" ),
        "QuarterNumber", FORMAT( [Date], "Q" ),
        "MonthYearNumber", FORMAT( [Date], "yy mm" ),
        "Month Year", FORMAT( [Date], "mmm yyyy" )
    )
 
DAX used to count projects created:
Count Created = CALCULATE( DISTINCTCOUNT('PT Full Vw Extended'[ID]), USERELATIONSHIP('PT Full Vw Extended'[Created], 'Calendar'[Date]))
This returns blank for me everytime. Any ideas?

@amitchandak How would one go about making this work inside of STAR schema? For example if I have a department table, employee table,  that connects to a bridge table that connects them together? Your solution works, but does not work with filtering with other tables. Like if I wanted to see hired employees by department etc. 

Hi @amitchandak This perfect for what I need - thank you. Had some trouble because im new to this (so please ignore previous comment)

Hi @amitchandak 

Nice Explanation.

Can you please provide the data source for this?

Then only I and My trainees can practice easily.

   Thank you.