Forum Discussion

lunarsoleils's avatar
lunarsoleils
Regular Visitor
3 years ago

Headcount DAX

Hello!   First post and beginner -   I am working on an HR Retention Dashboard and am struggling to calculate the Headcount. I have been following some tutorials from Solutions Abroad but my head...
"DAX"
HR
lunarsoleils's avatar
lunarsoleils
Regular Visitor
3 years ago

Hi Nathan, 

 

Thank you so much for your response, this is how my 'Employee Data' table and 'Calendar' table connected:

 

The expected headcount for January 2022 is 619. Ideally, headcount should include every active employee as of the date selected and exclude anyone who was terminated. 

 

Please let me know if you need further information and thank you again for the assistance!

 

Best, 

 

lunarsoleils's avatar
lunarsoleils
Regular Visitor
3 years ago

Also, this is how I have Count of Employees setup:

 

  • WinterMist's avatar
    WinterMist
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    lunarsoleils 

     

    Thanks for providing this information.

     

    First, you need an active relationship between your 'Employee Data' table & your 'Calendar' table.  Currently both of your relationships are inactive (dotted line).

    Can you make the relationship with "Hire/Rehire Date" active & leave the "Termination Date" as inactive?

     

    Second, your definition of active is as follows:

    "Headcount should include every active employee as of the date selected and exclude anyone who was terminated."

     

    If I'm interpreting this log correctly,

    We should include records WHERE :

    [Hire/Rehire Date] < MAX('Calendar'[Date])

    AND

    (
       [Termination Date] > MAX('Calendar'[Date]

       OR 

       [Termination Date] IS BLANK

    )

     

    If I do this however, I get a Headcount of 731 for January 2022.

     

     

    I don't have an easy way to verify that this is correct, with so much historic data, so I approached it from a different direction.

     

    The data shows that from 1980 through 2021, there were 732 employees with a Hire/Rehire Date, without any Termination Dates in those years.

    If I compare [Count of Employees] to [Headcount] for those years, it matches with 732.

     

    The very first time that a Termination Date appears is in January of 2022.  In Jan 2022, there are:

    - 22 records for Hire/Rehire Date

    - 16 records for Termination Date  

     

    So now if I isolate January 2022, I get 731.  This seems closer than 619.

     

    Are you able to verify 100% that 619 is the correct number.

    I'm not understanding how this can be correct with what I see in the data.

     

     

     

    Regards,

    Nathan