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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AGuero
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
v-qiuyu-msft
Community Support
Community Support

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.

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

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.