Headcount DAX

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