Forum Discussion

AGuero's avatar
AGuero
Frequent Visitor
8 years ago

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, depa...
v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
8 years ago

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

 

 

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

 

 

Create a measure: 

 

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

 

 

Best Regards,
Qiuyun Yu

AGuero's avatar
AGuero
Frequent Visitor
8 years ago

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

  • AGuero's avatar
    AGuero
    Frequent Visitor
    8 years ago

    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