topic Re: Headcount DAX in DAX Commands and Tips

Headcount DAX

lunarsoleils — Fri, 10 Mar 2023 16:22:38 GMT

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 headcount isn't accurate as it is still counting everyone after the date I have selected to filter from.

Here is the Sample Data

Here is the measure:

Headcount =

CALCULATE(

[Count of Employees],

FILTER( 'Employee Data'

, (

'Employee Data'[Hire/Rehire Date] < MIN('Calendar'[Date])

&& 'Employee Data'[Termination Date] > MAX('Calendar'[Date])

) || ISBLANK('Employee Data'[Termination Date])

))

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!

Re: Headcount DAX

WinterMist — Fri, 10 Mar 2023 17:04:29 GMT

lunarsoleils

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

Re: Headcount DAX

WinterMist — Fri, 10 Mar 2023 18:13:04 GMT

lunarsoleils

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

Re: Headcount DAX

lunarsoleils — Fri, 10 Mar 2023 20:50:14 GMT

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,

Re: Headcount DAX

lunarsoleils — Fri, 10 Mar 2023 20:51:46 GMT

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

Re: Headcount DAX

WinterMist — Fri, 10 Mar 2023 21:55:20 GMT

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]

[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