Hello I am stuck on how to finalize this formula with a lot of different parameters involved.
So I have an active count function that allows me to see active staff as shown below.
Employee Count =
VAR selectedDate =
MAX ('Date'[Date])
VAR result =
CALCULATE (
COUNTROWS ( 'Report Data'),
ALLSELECTED('Date'),
'Date'[Date]<= selectedDate,
ISBLANK('Report Data'[Termination_Date])
|| 'Report Data'[Termination_Date]>= selectedDate
)
Return
result
It works great! But what I need to take into consideration is Rehire date and termination dates.
The way the data is setup is when someone is terminated their termination date goes into the termination column. But if they are rehired, then the termination date goes into a column called Previous termination date. If they terminate again then the termination date is populated in the termination date column. And the previous termination is based on the original hire date.
So what I want to do is basically count active staff based on hire and rehire date, then if there is a rehire date look at previous terminate date and get the active count between those two. But if there is a second termination date, then take the original hire date and previous termination date and count active, then count a second time for Rehire and Termination date. So essentially counting the same person twice if they have two hire and termination dates. but only within their working time.
Table columns:
Full Name
Position
Hire Date
Rehire Date
Termination Date
Previous Termination date
From a basic perpective, I am just trying to get a total active count including hire and rehires.
Let me know if this is possible, I am not sure how to incorporate this into my initial active count by timeframe.
Thank you!
