March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
Solved! Go to 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.
Hi,
Try something like this:
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 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!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
23 | |
16 | |
15 | |
7 | |
6 |
User | Count |
---|---|
33 | |
29 | |
16 | |
13 | |
12 |