Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous

I have some questions:

  1. and if the end date is "31/12/3000"? You would check if the date is bigger than currently day. What change need in formula?
  2. to calculate FTE, I don´t see the formula?:(

thanks

@amitchandak This is brilliant, thank you very much!

 

I have a similar task, albeit with cases instead of employees, but also with a start and stop date, and your response has helped alot. Would there be any way to change the 'current employees' calculation around, so that it can answer the question: 

"How many current/active employees where there in year X?" 

 

In my case, i'm trying to figure out a way to show the number of active cases each year. 

My issue is exactly same as commented by @Oelgaard . I have found the solution to this. Someday I will post my solution to it on this forum.

Thanks.

I need the number of employees who have been there for at least 45 days in the previous month. So these are employees whose hired date is <=45 and terminated date is zero, or whose Terminated date is in the month in question but the hired date is >=45 days before the terminated date. I have now tried for three whole days to get this to work, but have now given up. Can anyone help? The basis was the measure "Last Period Employee" here from the example.

Thanks for the comment on my post... 

I am having trouble with CreatedProducts... its not splitting by date just giving me a round number before we even 

CreatedProduct = CALCULATE(COUNT('Subscription Product'[Id]), USERELATIONSHIP('Subscription Product'[CreatedDate], 'Date Table'[Date]))
 
 

You are superb. Only one issue please help me to count Average of Current Employees like month wise below is the example how want answer. please help.

 

I can able find Hired Employee,Terminated Employees and Current Employees using your measures.

Please help me how to calcualte Avg of Current Employees at every month.

 

Currently i am using AVg Head  forumla for Avg is :AVERAGEX(VALUES('Date'[Date (Month)]),[Current Employees]) but this formula gives Avg. at bottom, i want every month Avg.  Also help me to calucalte attrtion my excel formula for attrtion is (Terminated Employees/ Avg of Current Employees*12)/Current Month). 

Note i have large data and every thing in filter like year , Department etc. I am using excel pivot point. Your help can make my life easy. I’m eager to receive your help .🙂

 

LocationMumbai     
Filter YearMonthHired EmployeeTerminated EmployeesCurrent EmployeesAvg of Current EmployeesAttrition
2021Jan6455959(Terminated Employees/ Avg of Current Employees*12)/Current Month)
2021Feb86514010030%
2021Mar30916112030%
2021Apr68822114517%
2021May63727717210%
2021Jun33430619412%
2021Jul70637021916%
2021Aug971045724924%
2021Sep8845412819%
2021Oct66660131311%
2021Nov3146283427%
2021Dec32865236813%

 

Hi, 

 

This shows last years values e.g if i select Feb 21, this shows the values for Feb 20. But i want to show values for March 20.  How can i show this? 

 

var selectedDate = NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Date'[Date])))

Thank you so much amit, you are supber and life savior for me.

 

Please help me how to find two dates differnace in power BI.

Start Date	End Date	Date Difference	Excel Formula
1-Jan-12	30-Apr-21	9 Year 3 Month 29 Days 	DATEDIF(A2,B2,"Y")&" Year "&DATEDIF(A2,B2,"YM")& " Month "&DATEDIF(A2,B2,"MD")& " Days "
31-Dec-12	30-Apr-21	8 Year 3 Month 30 Days 	
15-May-14	30-Apr-21	6 Year 11 Month 15 Days 	
27-Sep-15	30-Apr-21	5 Year 7 Month 3 Days 	
26-Sep-16	30-Apr-21	4 Year 7 Month 4 Days 	
8-Feb-18	30-Apr-21	3 Year 2 Month 22 Days 	

 

Thanks a lot Amit. You save my day 🙂

Hi @amitchandak , Thank you very much for posting this solution.

It is excatly what I was looking for!

The pbix save me a lot of time too.

I would give you 100 kudos if I could 😊

Anonymous

Thanks @amitchandak , this really helped a lot.

 

I have few questions , will need your help here

1) Hires, i need to show the data for current FY - I am able to get the count but there is blank row reflecting which is summing up the rest of HC as below table, Can you guide here.

 

  
HiredMonth year
3000 
114Apr-21
90May-21
88Jun-21

 

note - I added the date table from Apr 21 to Apr 22

 

2) Current HC - i have to exclude the mobility case for the month based on start date (this is seperate column) on the same file... [terminated already taken care per the measure you haveshared]

Mobility Mobility Start
Transfer - Long Term5-Apr-2021
Short Term 
- 
Intercity6-Jun-21

This works perfectly (thank you!) when I only want to count the number of staff.

 

Unfortunately, I cannot modify it to SUM their availability to create a Full Time Equivalent count. I have the "Availability" column in my staff data where, for instance, a full time employee has a value of 1 and an employee who only works half of the time has a value of 0.5, but adding this up is beyond me!

 

I'd be grateful for any suggestions.

 

 

Chris

Thanks a LOT!

 

The only problem I'm currently having is being able to view by Yr-Qtr-Mon-Date. 

 

I have a very comprehansive DATE table that I use that includes various attributes including Yr-Week that I'm trying to incorporate.

 

Unfortunately your measure is based on Yr-Mon.

 

Any suggestions?

 

Thanking you in advance...

 

(Also - THANKS A LOT!)

Anonymous

How would I count the amount of employees with certain contract type (Full Time and Part Time) within a time period?

 

@Anonymous 

 

As long as you have a date based filter on the report page, you can use a measure based on this one. 

This one counts male employees, but the principle applies for Full Time (assume you have a column with that data). 

the 'trick' is the DatesBetween filter of the measure.  Since the page has a filter for May 2022, it will limit the rows in the pays table to those between 01/05/2022 a d 31/05/2022

 

PaidMales = CALCULATE(DISTINCTCOUNT(HRPayLines[myPayPerson]),HRPerson[gender] = {"Male"}, DATESBETWEEN(Calendar1[DATE], MIN(CalendarPeriod[YearStart]), Max(Calendar1[DATE])))