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:
