Determine Average Employee Count for each year & Count Total Terminations, to Derive Turnover %

[HRAnaly123]

Hi Wonderful People, 

I need help creating a rolling Turnover % Calculation for our HR Team. 

For Each Year and Month, 

The average number of Active Staff we had for each year, And the Total Terminations for Each Year, So I can use these two variables, to calculate the Turnover %.

This way, i can use a line chart with Year-Month & the Turnover % and Drill down by Division. 

I have Three Tables modelled below. 

1.) Employee Data Table = Employee ID | Hire Date | Termination Date | Division. (this table has all employees for the past 8 Years) 

2.) Date Table 1 = Connected to Hire Date

3.) Date Table 2 = Connected to Termination Date. 

My current Rolling Head Count Calculation= This calculation appears to be correct. 
"

CALCULATE(DISTINCTCOUNT(ADP_CurrentActive[EMP_CODE]),
FILTER(ADP_CurrentActive,ADP_CurrentActive[Termination Reason]<>"Employee did not commence"),

FILTER( VALUES( 'ADP_CurrentActive'[Hire Date]), ADP_CurrentActive[Hire Date] <= MAX( DATETABLE_DIM[Date] ) ),

FILTER( VALUES( ADP_CurrentActive[TERM_DATE]), OR( 'ADP_CurrentActive'[TERM_DATE] >= MIN( DATETABLE_DIM[Date]), ISBLANK( 'ADP_CurrentActive'[TERM_DATE]))))
"

If the Employee is not Terminated- I fill in the Blank Term Date with a Static Date of 01/01/2050. 

All Terminations Calculation = This Calculation is correct when i validate. 

"

CALCULATE(DISTINCTCOUNT(ADP_CurrentActive[EMP_CODE]),
FILTER(ADP_CurrentActive,ADP_CurrentActive[TERM_DATE]<>01/01/2050),

USERELATIONSHIP(ADP_CurrentActive[TERM_DATE],TERMDATETABLE_DIM[Date]))

What do you think would be the best approach to this. 

I think it would be best to create a Custom Table, for each Year/Month, with Total Staff at End of Year, and Total Terminations at End of Year, then add a calculated column from there to determine the Turnover %. 

But i do not know how to do this 😞

Thank you

[Anonymous · Accepted solution]

Hi @HRAnaly123

Based on your description, I think your calculations are correct. You've calculated your annual and monthly rolling headcount and termination totals. To calculate turnover, simply divide the total number of terminations by the number of rolling heads and multiply by 100.

You can create a custom table using the DAX function SUMMARIZE. This function allows you to group your data by year and month, and apply calculations to each group. For example, you can use this formula to create a custom table with the rolling head count, total terminations, and turnover % for each year and month:

CustomTable = 
SUMMARIZE(
    ADP_CurrentActive,
    DATETABLE_DIM[Year],
    DATETABLE_DIM[Month],
    "Rolling Head Count", [Rolling Head Count Calculation],
    "Total Terminations", [All Terminations Calculation],
    "Turnover %", DIVIDE([Total Terminations], [Rolling Head Count], 0) * 100
)

You can then use this custom table to create a line chart with Year-Month on the x-axis and Turnover % on the y-axis. You can also add a slicer or a filter to drill down by Division.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[gmsamborn]

I would like to help but I'm a little unclear about your requirements.

Can you show the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

I hope this helps.

[HRAnaly123]

RandEMPCode  Hire Date     Termination Date

I have written three measures

" Current Active Staff = CALCULATE(DISTINCTCOUNT(SampleData[RandEMPCode]), FILTER(SampleData,SampleData[Termination Date]<>DATE(2030,01,01)))

// This should reflect, the Current Number Active Staff, as we are excluding those with a Termination Date of 01/01/2030)" 

Total Terminations = CALCULATE(DISTINCTCOUNT(SampleData[RandEMPCode]), FILTER(SampleData,SampleData[Termination Date]<>01/01/2030),

USERELATIONSHIP(SampleData[Termination Date],DateTable[Date]))

And a rolling Head Count

RollingHeadCount = CALCULATE(DISTINCTCOUNT(SampleData[RandEMPCode]),

    FILTER( VALUES( SampleData[Hire Date]), SampleData[Hire Date] <= MAX( DateTable[Date] ) ),

    FILTER( VALUES( SampleData[Termination Date]), OR( SampleData[Termination Date] >= MIN( DateTable[Date]), ISBLANK( 'SampleData'[Termination Date]))))

    // Using the Hire Date and Termination Date, Determine on any given Day, the total number of people who were active and not terminated.

    // I have replaced anyone WITHOUT a termination date, with a static date of "01/01/2030"

    // This calcualtion is almost correct, but maybe i have done something wrong or am misinterpreting the data.

The issue is, My Current Active Staff Calculation shows 2159 at present date. 

But on the Line chart for the rolling head count. If i Filter Down to Todays Date, I get a different value. 

Maybe i am just interpreting this incorrectly.