Headcount over time

[cgallegos]

Hello,

I am needing help calulating employee headcount overtime using hire and term dates. However, some of our employees have both a hire and rehire date. For example:

ID	EE	Status	Hire Date	Rehire Date	Term Date
123	Tom	Active	09/13/2021	01/17/2022	12/02/2021
1234	Ben	Active	06/25/2021	01/24/2022	10/21/2021
1235	Bill	Active	09/25/2017	01/12/2022	06/25/2021
1236	Greg	Active	06/26/2020	01/03/2022	07/18/2020
12355	Kay	Termed	07/20/2019	08/20/2020	1/1/2022

I would need a formula that counts Tom between 9-13-21 and 12/02/21 then again from 1/17/22 on. 

We only store one term date, so Kay's term date reflects the more recent termination date.

Does anyone know how I could do this?

Thank you in advance!

[ValtteriN]

Hi,

Try something like this:

var c_date = MAX('Calendar'[Date])

var _value = COUNTROWS(

FILTER(Hires,Hires[Hire Date]<=c_date &&Hires[Term Date]>=c_date))

+

COUNTROWS(

FILTER(Hires,Hires[ReHire Date]<=c_date&&Hires[Rehire Date]>Hires[Term Date]))

return

if(_value=BLANK(),0,_value)

The IF at the end is optional and you can just use _value.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[cgallegos]

Thank you for your help, I appreciate it. This did not work, however, i don't believe it's on your end. I think the data I have is more complex than I was thinking and will require us to use a different report. 

For each action such as a Termination or Hire, the employee will have a new row. Is there another way to capture headcount like this? I am thinking something like finding the 1st instance of Hire and 1st instance of termination, then seeing if they were active at the dashboard selected point in time? If not, then check the 2nd instance of hire/rehire and 2nd instance of termination, check to see if they were active at the dashboard selected point in time?

Green column is what I need to build in PBI.

Empl ID	Name	Action	Effective Date	Occurence
252	Chuck	HIRE	03/09/2020	1
252	Chuck	TERM	08/10/2020	1
252	Chuck	HIRE	04/10/2021	2
252	Chuck	TERM	08/27/2021	2
253	Kay	HIRE	04/15/2020	1
253	Kay	TERM	08/14/2020	1
253	Kay	HIRE	09/08/2020	2

Then maybe I can create another table that looks up their various hire and term dates

Empl ID	1st Hire	1st Term	2nd Term	2nd Term
252	03/09/2020	08/10/2020	04/10/2021	08/27/2021
253	04/15/2020	08/14/2020	09/08/2020

Please let me know if this is something you can help with or if you need additional background on it.

Thank you!

[v-zhangti · Accepted solution]

Hi, @cgallegos 

You can refer to the following methods.

Occurence = 
CALCULATE (
    COUNT ( 'Table'[Action] ),
    FILTER (
        'Table',
        [Name] = EARLIER ( 'Table'[Name] )
            && [Effective Date ] <= EARLIER ( 'Table'[Effective Date ] )
            && [Action] = EARLIER ( 'Table'[Action] )
    )
)

Is this the result you expect?  If the method I provided above can't solve your problem, what's your expected result? You can advance a display chart of your desired results or a simple PBIX file for testing.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.