Forum Discussion

dgkallan's avatar
dgkallan
Helper II
2 years ago
Solved

HR Turnover Rate

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...
Need Help
  • dgkallan's avatar
    dgkallan
    2 years ago

    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's avatar
EylesIT
Resolver II
2 years ago

dgkallan, this is my suggested solution.

 

I am using the following test data of 11 employees:

 

EmployeeID  HireDate  TerminationDate

E0011 Jan 202031 Dec 2022
E0021 Jan 2020 
E0031 Jan 20211 May 2023
E0041 Jan 2022 
E0051 Jan 20231 Jun 2023
E0061 May 2023 
E0071 Jun 202330 Jun 2023
E0081 Jul 2023 
E0091 Aug 2023 
E0101 Sep 20231 Oct 2023
E01122 Mar 202219 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's avatar
    dgkallan
    Helper II
    2 years ago

    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:

    MonthTermsBeg of PeriodEnd of Period
    Jan001
    Feb012
    Mar022
    Apr122
    May023
    Jun033
    Jul132
    Aug022
    Sep023
    Oct134
    Nov145

     

    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:

    MonthTermsBeg of PeriodEnd of Period
    Jan01 (should be 0)1
    Feb02 (should be 1)3 (should be 2)
    Mar03 (should be 2)3 (should be 2)
    Apr122
    May03 (should be 2)4 (should be 3)
    Jun04 (should be 3)4 (should be 2)
    Jul132
    Aug03 (should be 2)3
    Sep03 (should be 2)3
    Oct134
    Nov13 (should be 4)4 (should be 5)
    Dec0 (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