cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

How do you keep track of (for example) contract history per employee in this data model?

The graph shows the trend of employees with the *actual* values for contract type, but employees can switch type over time.
I am curious if there is a way to track history with this data model, because the model is really lean.

At the moment we use a quite large model, but its possible to track employee history and all data can be combined (-/+ 100 variables). For us, the "real" history is essential for HR analytics.

This is so great

To count the Current Employee, i have used the dax suggested by you. But i am not getting proper output. I have created date table with Financial Year Calendar. Pls help.

Current Employees = CALCULATE(COUNTx(FILTER(Sheet1,Sheet1[Joining Date] <= max('FY'[Date]) && (ISBLANK(Sheet1[Left Date]) || Sheet1[Left Date] >max('FY'[Date]))), (Sheet1[Personnel No.])),CROSSFILTER(Sheet1[Joining Date],'FY'[Date],None))

hi @BhaveshSavla , I'm not sure if the FY is causing this, try changing it to max('Date'[Date]) instead.

Hello,

Thank you so much for this. Really helpful. Question. How should I edit  "current employee" if I want to count only number of  current employ that has been employeed for more than 200 days in each each month? Like a rolling total of employees with the company for more than 200 days.

Hi @amitchandak ,

I'm trying to use it for calculation of open cases in given perion. average in month.

the only problem I have is that in my project case can be solved or closed so I have 3 columns with date:

Start date

Resolved date

Closed date (case wanst resolved but closed)

Any ideas how to work with such scenario?

Irek

@Irek , All three dates will join with the date table there will two inactive. And then you can create measures. If there is some specific measure you want to provide. please share some details

@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 Name ID Created Start Date Delivered Date Account Analysis 1 8/1/2022 8/15/2022 11/13/2022 Sales Analysis 2 9/26/2022 10/1/2022 Forecast Analysis 3 10/4/2022 10/10/2022 11/1/2022 Report Clean Up 4 11/22/2022 Customer Dashboard 5 7/8/2022 7/21/2022

DAX used to make date table:

Calendar =
VAR _calendar =
CALENDARAUTO()
RETURN
_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)

Nice Explanation.

Can you please provide the data source for this?

Then only I and My trainees can practice easily.

Thank you.

Very helpful article which helped my understanding of using/inactivating relationships for accurate headcounts!  Many thanks!

Hi sir how to get the Beggining employee for this table?

Beginning Employee = Count of all employees that are active for the period (active being, hire date >= the month-year filter and not separated on the same period) is this right?

Thank you.

Anonymous

@amitchandak I know this article is older, but I am having problems with getting the correct Current Employee count.  We currently have 509 active employees and the results of your query is giving me a far less number. The monthly hired and termination numbers are correct though. I am using the calculations you've provided (including the corrected query for current Employees including accounting for ENDOFMONTH).

Do you have any ideas on what may be going on here? Every article I have found on this does not solve my problem and gives me similar results where the monthly active employee count is far below what it actually should be.

Thanks for any assistance you can provide!

Michelle

Top Kudoed Posts
Latest Articles
Archives