cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AGuero Frequent Visitor
Frequent Visitor

How to handle Active Employees

New Power BI user - glad to be a part of this helpful community.

 

I'm trying to put together metrics related to employee headcount (turnover, employees by type (i.e. exempt, non exempt), age, department, etc.  Many of these calculations require that I calculate a rate (turnover rate, promotion rate) and thus I need to have a total count of active employees in the denominator.

 

It seems like the best case scenario would be to connect to our HRIS system so I can get this information real time - if anyone has experience with this please let me know.  Right now this is not an option. I am trying two "work-arounds" that both present issues and would love the community's feedback:

 

(1) Maintain a list of active employees with an "effective date" column - update this monthly.  For example, pull list of all active employees for October 2017 and date stamp it October 2017.  Repeat for each subsequent month. 

PRO: Allows me to use the effective date as a filter and write measures (total active employees, average employees in a particular year) based on effective date.  It's also easy to slice by a variety of employee characteristics that change over time (job title, department)

CON: When I connect this to a calendar table, it doesn't really behave well - i.e. it thinks there are 12+ times the number of active employees in the year 2016.  It also limits my ability to use a slicer and makes measures very manual (i.e. if I want average employees for 2017, I can't use Beginning of year + End of year/2 and other date funcations). 

 

2) Pull a list of all change history (hires, terminations, re-hires) and somehow use a similar funcation to this one to calculate active employees based on a particular date. 

 

PRO: Would allow me to link to the calendar table and more easily calculate rates, see changes over time, and make report more user friendly.

CON: Not sure I could even do this given that someone may have multiple termination dates (see example data below).  Also, I don't think I could filter by employee characteristics that change unless I pull the entire change history.

 

My change history log looks something like this:

 

Name ID - Effective Date - Change Reason - Change Reason Descriptions - Title -  Department

EMP 1 - 1/1/2015 - HIRE - New Hire - Analyst - Finace

EMP1 - 12/1/2015 - CHANGE - Dept Change - Analyst - Accounting

EMP1 - 12/5/2015 - TERMINATION- Quit - Analyst - Accounting

EMP1 - 3/16/2016 - REHIRE - Rehire - Sales Associate - Sales

EMP1 - 11/1/2017 - TERMINATION - Quit - Sales Associate - Sales

 

 

Please help and thanks in advance!

 

 

3 REPLIES 3
Moderator v-qiuyu-msft
Moderator

Re: How to handle Active Employees

Hi @AGuero,

 

In your scenario, you can go to the Query Editor add a index column from 1. Then go to report, create below calculated columns: 

 

IfAct = IF('Table1'[Change Reason] in {"Hire", "Rehire"},1, IF(Table1[Change Reason]="TERMINATION",0,BLANK()))

NewGroup = CALCULATE(MAX(Table1[Index]),FILTER('Table1',Table1[Index]<=EARLIER(Table1[Index]) && 'Table1'[Name ID]=EARLIER(Table1[Name ID])&& Table1[IfAct]=1))

 

1.PNG

 

Then create a calculated table:

 

Table = SUMMARIZE('Table1',Table1[NewGroup],"start",CALCULATE(MAX('Table1'[Effective Date]),FILTER(ALL(Table1),Table1[NewGroup]=MAX(Table1[NewGroup]) && Table1[IfAct]=1)),"end",CALCULATE(MAX('Table1'[Effective Date]),FILTER(ALL(Table1),Table1[NewGroup]=MAX(Table1[NewGroup]) && Table1[IfAct]=0)))

 

2.PNG

 

Create a measure: 

 

ActiveCount = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[start]<=MAX('Calendar'[Date]) && 'Table'[end]>MAX('Calendar'[Date])))

 

3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AGuero Frequent Visitor
Frequent Visitor

Re: How to handle Active Employees

Wow, thank you so much for this reply.  I may have a few follow up questions - but will try to execute this week. 

AGuero Frequent Visitor
Frequent Visitor

Re: How to handle Active Employees

Qiuyun,

 

I'm not sure I am following the logic on this once you get to the "newgroup" - does this just count the number of active employees before that date?  If so doesn't this double count those that have both a hire and rehire?

 

Also - I don't think this would allow me to slice by other descriptors like department, gender, etc., right? 

 

Thanks in advance