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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
cgallegos
Regular Visitor

Headcount over time

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:

 

IDEEStatusHire DateRehire DateTerm Date
123TomActive09/13/202101/17/202212/02/2021
1234BenActive06/25/202101/24/202210/21/2021
1235BillActive09/25/201701/12/202206/25/2021
1236GregActive06/26/202001/03/202207/18/2020
12355KayTermed07/20/201908/20/20201/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!

 

1 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] )
    )
)

vzhangti_0-1644374273230.png

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.

 

View solution in original post

3 REPLIES 3
ValtteriN
Super User
Super User

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/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 IDNameActionEffective Date Occurence
252ChuckHIRE03/09/20201
252ChuckTERM08/10/20201
252ChuckHIRE04/10/20212
252ChuckTERM08/27/20212
253KayHIRE04/15/20201
253KayTERM08/14/20201
253KayHIRE09/08/20202

 

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

Empl ID1st Hire1st Term2nd Term2nd Term
25203/09/202008/10/202004/10/202108/27/2021
25304/15/202008/14/202009/08/2020 

 

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

 

Thank you!

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] )
    )
)

vzhangti_0-1644374273230.png

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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors