Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lunarsoleils
Regular Visitor

Headcount DAX

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.

lunarsoleils_0-1678465327709.png

Any assistance is greatly appreciated!

5 REPLIES 5
WinterMist
Impactful Individual
Impactful Individual

@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?

 

WinterMist_0-1678467396010.png

 

WinterMist_1-1678467528474.png

WinterMist_2-1678467653118.png

 

WinterMist_3-1678467697754.png

 

Also, how is your [Count of Employees] measure defined?

I defined it as follows:

WinterMist_4-1678467848936.png

 

Regards,

Nathan

@lunarsoleils 

 

As an additional note, the first part of the filter (as is) will never return results for the given filter context.

 

WinterMist_0-1678471732314.png

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:

 

WinterMist_1-1678471928197.png

 

Regards,

Nathan

 

 

Hi Nathan, 

 

Thank you so much for your response, this is how my 'Employee Data' table and 'Calendar' table connected:

 

lunarsoleils_0-1678480032400.png

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:

lunarsoleils_0-1678481492496.png

 

@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.

 

WinterMist_0-1678482993301.png

 

WinterMist_1-1678483060151.png

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.

WinterMist_2-1678483992895.png

 

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.

 

WinterMist_3-1678484766450.png

 

 

Regards,

Nathan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors