Headcount DAX

Question

Hello!&nbsp;First post and beginner -&nbsp;I am working on an HR Retention Dashboard and am struggling to calculate the Headcount. I have been following some tutorials from&nbsp;Solutions Abroad&nbsp;but my headcount isn't accurate as it is still counting everyone after the date I have selected to filter from.&nbsp;&nbsp;Here is the&nbsp;Sample Data&nbsp;&nbsp;Here is the measure:&nbsp;Headcount = &nbsp; &nbsp; CALCULATE( &nbsp; &nbsp; &nbsp; &nbsp; [Count of Employees],&nbsp; &nbsp; &nbsp; &nbsp; FILTER( 'Employee Data'&nbsp; &nbsp; &nbsp; &nbsp; , (&nbsp; &nbsp; &nbsp; &nbsp; 'Employee Data'[Hire/Rehire Date] &lt; MIN('Calendar'[Date]) &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; 'Employee Data'[Termination Date] &gt; MAX('Calendar'[Date]) &nbsp; &nbsp; &nbsp; &nbsp; ) || ISBLANK('Employee Data'[Termination Date])&nbsp; &nbsp; ))&nbsp;So for example, when I click January 2022 - it is still counting all of the new hires after that date.Any assistance is greatly appreciated!

wintermist · Answer

lunarsoleils&nbsp;&nbsp;Thanks for providing this information.&nbsp;First, you need an active relationship between your 'Employee Data' table &amp; your 'Calendar' table.&nbsp; Currently both of your relationships are inactive (dotted line).Can you make the relationship with "Hire/Rehire Date" active &amp; leave the "Termination Date" as inactive?&nbsp;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."&nbsp;If I'm interpreting this log correctly,We should include records WHERE :[Hire/Rehire Date] &lt; MAX('Calendar'[Date])AND(&nbsp; &nbsp;[Termination Date] &gt; MAX('Calendar'[Date]&nbsp; &nbsp;OR&nbsp;&nbsp; &nbsp;[Termination Date] IS BLANK)&nbsp;If I do this however, I get a Headcount of 731 for January 2022.&nbsp;&nbsp;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.&nbsp;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.&nbsp;The very first time that a Termination Date appears is in January of 2022.&nbsp; In Jan 2022, there are:- 22 records for Hire/Rehire Date- 16 records for Termination Date&nbsp;&nbsp;&nbsp;So now if I isolate January 2022, I get 731.&nbsp; This seems closer than 619.&nbsp;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.&nbsp;&nbsp;&nbsp;Regards,Nathan

wintermist · Answer

lunarsoleils&nbsp;&nbsp;How is your 'Employee Data' table connected to your 'Calendar' table (date table) in the model?&nbsp;I downloaded your sample data, created my own date table, and then created the same measure as shown.&nbsp; But when I select January 2022, the Headcount shows 18.&nbsp; What is the expected result for Headcount in January 2022?&nbsp;&nbsp;&nbsp;&nbsp;Also, how is your [Count of Employees] measure defined?I defined it as follows:&nbsp;Regards,Nathan

wintermist · Answer

lunarsoleils&nbsp;&nbsp;As an additional note, the first part of the filter (as is) will never return results for the given filter context.&nbsp;For example, there are no records in January 2022 where:- Hire/Rehire Date&nbsp; &lt; 1/1/2022 AND- Termination Date &gt; 1/31/2022&nbsp;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].&nbsp;The following shows how the first part of the filter returns no results when the ISBLANK clause is commented out:&nbsp;&nbsp;Regards,Nathan&nbsp;&nbsp;

lunarsoleils · Answer

Hi Nathan,&nbsp;&nbsp;Thank you so much for your response, this is how my 'Employee Data' table and 'Calendar' table connected:&nbsp;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.&nbsp;&nbsp;Please let me know if you need further information and thank you again for the assistance!&nbsp;Best,&nbsp;&nbsp;

lunarsoleils · Answer

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

Forum Discussion

Headcount DAX

5 Replies

Related content

DAX Formula for Beginning and Ending Headcount

Average headcount DAX and unlinked calendar

Headcount, Joiners, leavers and Mobility of employees.

Understanding Headcount (Dax for Allexcept, All)

Headcount