Good Day All,
I have previously posted regarding this same project and have corrected my data model as suggested in that post. Which necessitated that I solve this in a different manner than provided in the original Staff Calculation post that brought me here.
Here is the scrubbed PBIX file after I improved my data model.
HR_CurrentStaff.pbix (283.1 KB)
Here is the data model:
In the calculation of current staff, employees with Hire dates and Rehire dates prior to the selected period are being counted twice. The calculations for Hire / ReHire / Terminations are calculated with the very simple formula below, changing only the action name:
Hires = //count all hiring actions
CALCULATE(COUNTROWS(HR_Actions),
HR_Actions[ACTION] = “HIRE”
)
Running totals for the actions are calculated using variations of this formula, again only changing the action reference:
RT_Hires = //RunningTotal of all Hires
CALCULATE([Hires],
FILTER(ALL(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])
)
)
These set’s of calculations all appear to be calculating correctly. To calculate the current staff I created this, again, very simple formula:
Current Staff = ([RT_Hires] + [RT_REHIRES]) - [RT_Terms]
The issue appears to be with employees that have both a Hire action and a RE-Hire action that happen prior to the selected date. It appears these employees are being counted twice. as show here:
I’m having trouble getting this condition to count only once. I have tried this formula, however, it creates other issues and drives my numbers farther off course as it appears to remove all re-hires.
STAFF V2 =
IF(
AND(CALCULATE([RT_Hires],
FILTER(ALLSELECTED(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])
)),
CALCULATE([RT_REHIRES],
FILTER(ALLSELECTED(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])
))),
[Current Staff]- [RT_REHIRES],
[Current Staff]
)
A nudge in the right direction would be appreciated. I’m working through the UBG to Dax at the moment, and intend to continue thru the advanced course, but I need to get this issue resolved for my management.
*** EDIT: I solved my own issue. The problem was with the underlying data. The personnell that were double counted had no termination date in the HR Actions table. Terminations start in the file in 2020, however hire data goes as far back as 1990. The employees that were being double counted showed a rehire date without a termination date. When I corrected the actions file to include a termination date for those records, my problem disappeared.
Another issue with the model and underlying data…in reality there was nothing wrong with my initial formula to calculate current staff.
If this can be deleted, please do so as to not clutter the forum with me talking to myself. 
Best Regards,
Jamie