cancel
Showing results for
Did you mean:

HR Analytics - Active Employee, Hire and Termination trend

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:

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.

@nhoward, thanks for sharing your experience.

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.

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

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

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.

@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?

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

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

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 .🙂

 Location Mumbai Filter Year Month Hired Employee Terminated Employees Current Employees Avg of Current Employees Attrition 2021 Jan 64 5 59 59 (Terminated Employees/ Avg of Current Employees*12)/Current Month) 2021 Feb 86 5 140 100 30% 2021 Mar 30 9 161 120 30% 2021 Apr 68 8 221 145 17% 2021 May 63 7 277 172 10% 2021 Jun 33 4 306 194 12% 2021 Jul 70 6 370 219 16% 2021 Aug 97 10 457 249 24% 2021 Sep 88 4 541 281 9% 2021 Oct 66 6 601 313 11% 2021 Nov 31 4 628 342 7% 2021 Dec 32 8 652 368 13%

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.

``````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 🙂

Top Kudoed Posts
Latest Articles
Archives