Hi there,
I am trying to redesign some of our calcs to make recruitment planning more accurate based on trending attrition values as well as planned attrition plans.
We use a pre-determined % to calculate attrition p/queue we have to work out potential recruitment in future months. For example: Applying a 3% loss in heads on our current staff in Jan-24, and the further 3% in Feb-24 and so on.
The pre-determind % used will change with each fiscal cycle so the table could change annually. This is why I plan to import the table and the planned % values for each fiscal cycle. This also allows for manual adjustments for respective queues and months.
This is only used on Current and Future months as we will already calculate known attrition into the existing staff numbers. To that we will usually not apply any additional attrition in the month that we are in as we should in theory have confirmed most of the attrition taking place in the month. Similarly we will only apply half a months attrition in the next month as we should have already confirmed some of the attrition to take place.
Calculation One
The above can open up room for error so I want to try move away from this and rather calculate the difference between the planned attrition and known attrition in each month so that we are keeping within planning. The % Attrition would then be applied monthly to the previous months headcount to show the impact on planned attrition.
Calculation Two
Additionally I would also as a secondary calculation also want to calculate the last 6 months trending attrition p/queue and apply this % against each month while also taking into account all known attrition and ensuring we make adjustments to the trending values used there as well.
Existing DAX Calculations
Current Employees =
CALCULATE(
COUNTX(
FILTER(AgentTracking_Table,
AgentTracking_Table[Start Date] <= MAX('Calendar'[Date]) &&
(ISBLANK(AgentTracking_Table[End Date]) ||
AgentTracking_Table[End Date] > MAX('Calendar'[Date]))),
(AgentTracking_Table[Agent Entity])),
CROSSFILTER(AgentTracking_Table[Start Date],'Calendar'[Date],None))
Terminated Employees = CALCULATE(
COUNT(AgentTracking_Table[Agent Entity]),USERELATIONSHIP(AgentTracking_Table[End Date],'Calendar'[Date]),not(ISBLANK(AgentTracking_Table[End Date])))
Trending Attrition % = [Terminated Employees]/[Current Employees]
Below is an example of a view I am looking for. You can see how 'Queue 10' heads gradually drop month on month factoring in potential attrition monthly.
| Skill | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 | Jul-24 |
| Queue 9 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 2 | 2 |
| Queue 10 | 35 | 34 | 33 | 32 | 31 | 31 | 30 | 29 | 28 |
Example of planned attrition table
| Skill | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 | Jul-24 |
| Queue 1 | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% |
| Queue 2 | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% |
| Queue 3 | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% |
| Queue 4 | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% |
Example of staff file
| Agent Name | Start Date | End Date |
| Bob | 01/02/2023 | |
| Jane | 01/06/2023 | |
| Fred | 01/12/2022 | |
| Bill | 01/01/2023 | 17/07/2023 |
| Jane | 01/02/2023 | 21/07/2023 |
Hope this helps explaining, look forward to solutions 🙂
