Forum Discussion
Headcount DAX
How is your 'Employee Data' table connected to your 'Calendar' table (date table) in the model?
I downloaded your sample data, created my own date table, and then created the same measure as shown. But when I select January 2022, the Headcount shows 18. What is the expected result for Headcount in January 2022?
Also, how is your [Count of Employees] measure defined?
I defined it as follows:
Regards,
Nathan
As an additional note, the first part of the filter (as is) will never return results for the given filter context.
For example, there are no records in January 2022 where:
- Hire/Rehire Date < 1/1/2022 AND
- Termination Date > 1/31/2022
You can probably just remove this piece from the measure.
The entire filter is resting only on the second part:
ISBLANK('Employee Data'[Termination Date].
The following shows how the first part of the filter returns no results when the ISBLANK clause is commented out:
Regards,
Nathan
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,
Also, this is how I have Count of Employees setup:
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
