HR Turnover Rate

[dgkallan]

This one really has my mind in circles.  I need to produce a turnover rate.  The formula for this is the number of people that left over the period selected (say one year or one month) divided by the average of (people there are the beginning of that period) and (people at the end of the period.  

So, for October if one person left and you had 9 people there on 10/1 and 11 people there on 10/31, it would be 1 divided by 10 or 10%.  For the year if 10 people left, it would be the average of the number of people there on 1/1/2023 and the number of peopler there on 12/31/2023.  It needs to be dynamic based on whatever period is pulled.

For the data, the relevant parts are as follows:

Employee ID = Unique employee number

Status = Active, Inactive, etc.

Termination Date = Date they left

Hire Date = Date they were hired

The report would be run at the end of the month.  I created a customer field called Turnover, which basically says that if the Termination Date is in the same month as the Report Date, mark that as a "1" else "0".  This allows me to count the turnovers well.

To get the Beginning of the month, I take the Active count + Turnover count (they were they at the beginning of the month) + New Hires (which is calcluated as people hired during that month).  

To get the end of the month, I grab anyone marked as active.  

Within a given month it's pretty easy, just create a measure for Turnover / Average(Begining of the Month and Active) - though I don't know the syntax for that yet.  This shold be give me the turnover rate for the month once I drop in the month as an attribute. If you know the syntax for that that would be helpful.

But here is where I"m really scratching my head.  I don't know how to do a dynamic turnover rate.  If they wanted to pick for this year, I don't know how to grab the January Beginning of Month and December End of Month.  Perhaps I need to structure my data differently?

Any help is greatly appreciated

[dgkallan · Accepted solution]

Thanks to @BA_Pete I was able to solve this!

Avg Employees =

VAR startdate = MIN(DateTable[Date])

VAR enddate = MAX(DateTable[Date])

VAR EmpsatStart =

CALCULATE (

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

         TestEmployees,

         TestEmployees[Hire Date] < startdate

         && (TestEmployees[Term Date] >= startdate || ISBLANK(TestEmployees[Term Date]))

         )

    )

VAR Hires =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Hire Date] <= enddate && TestEmployees[Hire Date] >= startdate)

    )

VAR Terminations =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Term Date] <= enddate && TestEmployees[Term Date] >= startdate)

)

    RETURN DIVIDE(EmpsatStart + (EmpsatStart + Hires - Terminations), 2, 0)

[EylesIT]

@dgkallan, this is my suggested solution.

I am using the following test data of 11 employees:

EmployeeID  HireDate  TerminationDate

E001	1 Jan 2020	31 Dec 2022
E002	1 Jan 2020
E003	1 Jan 2021	1 May 2023
E004	1 Jan 2022
E005	1 Jan 2023	1 Jun 2023
E006	1 May 2023
E007	1 Jun 2023	30 Jun 2023
E008	1 Jul 2023
E009	1 Aug 2023
E010	1 Sep 2023	1 Oct 2023
E011	22 Mar 2022	19 Oct 2023

I create a dimDate table with all dates from 1/1/2020 to 31/12/2099.

Then I add a slicer to the report and set it to use the Year, Qtr, and Month date hierarchy from the dimDate table.

I create a measure called [Terminated in period] which will return the number of employees that had a termintation date within the selected slicer dates:

Terminated in period = 
    VAR startdate = MIN(dimDate[Date])
    VAR enddate = MAX(dimDate[Date])
    VAR terminated =
        CALCULATE(
            DISTINCTCOUNT(YourTable[EmployeeID]),
            YourTable[TerminationDate] >= startdate,
            YourTable[TerminationDate] <= enddate
        )
    RETURN terminated

Then I create a measure called [Employees in period] which returns the numer of employees that were employed within the slicer dates. To do this, we need to find employees whose HireDate and TerminationDate range overlaps the selected slicer date range:

Employees in period = 
    VAR startdate = MIN(dimDate[Date])
    VAR enddate = MAX(dimDate[Date])
    VAR employees =
        CALCULATE(
            DISTINCTCOUNT(YourTable[EmployeeID]),
            YourTable[HireDate] <= enddate,
            YourTable[TerminationDate] >= startdate || ISBLANK(YourTable[TerminationDate])
        )
    RETURN employees

Next I create a measure called [Turnover] which divides these two:

Turnover = DIVIDE([Terminated in period] , [Employees in period] )

I added a couple of tables visuals to the report; one showing at EmployeeID level, and the other showing at Month level.

Here are the results when Oct 2023 is selected:

Here are the results when 2023 Qtr 2 is selected:

Here are the results when all of 2023 is selected:

I believe this will solve your problem. But I have just noticed this question is in the Power Query forum, and I have given my answer using Power BI. Hopefully you can still use these techniques in Power Query.

[dgkallan]

First off, thank you so much for the response.  

I build the measures in PowerBI as you said and I was able to get some data, but it was laid out funky.  I'm using a sample data file where I have 9 rows.  When I used your suggestions I had 5 employees not attached to any month as the employees in period and I one turnover in each month (this is correct - in the data there was a termination in the April, July, Oct, and Nov).  So, turnover calculates correctly.  I'm also able to use your code to calculate hired people correctly, but when I try to introduce beginning of period people the date attachments go wrong.

The other important note is that the formula for turnover rate is turnover divided by average of people there at the beginning of the period (where that be month or year) and end of the period (whether that be month or year).  So, hire date would really only be relevant to exclude those from the beginning of the period measure since they would be active but they weren't there at the beginning.  People that were terminated in that period were there at the beginning of the period, but not at the end.  Beginning of period are the terminated people + Active people - hired period.  End of period are the Active people.

I think it would be fair to calculate the beginning of the period as the distinctcount of employees where the hire date was before the start date of the period we're looking at AND where they either didn't have a termination date or their termination date was after the end date of the period we're looking at.  I tried doing it like this but I get 0 results:

Beginning of Period Employees =

VAR startdate = MIN(DateTable[Date])

VAR enddate = MAX(DateTable[Date])

VAR employees =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    TestEmployees[Hire Date]<startdate &&

    (ISBLANK(TestEmployees[Term Date]) || TestEmployees[Term Date]>enddate)

    )

    RETURN employees

For the end of the period, it would be anyone hired after the start of the period and before the end period less people who terminated during that period plus any employees where the hire date was before the start of the period.  I couldn't figure how to do this one, but if the measures where there it would be Beginning of Period minus Terminated + Hired

I would then need to average the beginning of the period and the end of the period as the denominator and use terminations as the numerator.

I should be getting this data based on my data:

Month	Terms	Beg of Period	End of Period
Jan	0	0	1
Feb	0	1	2
Mar	0	2	2
Apr	1	2	2
May	0	2	3
Jun	0	3	3
Jul	1	3	2
Aug	0	2	2
Sep	0	2	3
Oct	1	3	4
Nov	1	4	5

Instead I'm getting the below.  My End of Period data would be correct if the Beg of Period were correct, so that's where the problem is:

Month	Terms	Beg of Period	End of Period
Jan	0	1 (should be 0)	1
Feb	0	2 (should be 1)	3 (should be 2)
Mar	0	3 (should be 2)	3 (should be 2)
Apr	1	2	2
May	0	3 (should be 2)	4 (should be 3)
Jun	0	4 (should be 3)	4 (should be 2)
Jul	1	3	2
Aug	0	3 (should be 2)	3
Sep	0	3 (should be 2)	3
Oct	1	3	4
Nov	1	3 (should be 4)	4 (should be 5)
Dec	0 (shouldn't have any data here at all)	5 (shouldn't have any data here at all)	5 (shouldn't have any data here at all)

Again, thank you so much.  Please continue to help - I'm really stuck here