Assistance with Current Staff Calculations

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. :flushed:

Best Regards,
Jamie

Hi @JBright, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @JBright

If your issue is resolved, request you to please mark any of post either yours/EDNA as Solution.

Thanks
Ankit J