Forum Discussion

TomTomTom's avatar
TomTomTom
Icon for Helper II rankHelper II
5 years ago
Solved

Calculating Turnover Rate in DAX ( But: Using Average Employed Per Day )

I would like to calculate staff turnover rate in DAX, but the formula we are using is slightly different to the cases I've seen online:

 

Leavers in given period of time

divided by

Average headcount in same period of time

 

It is the average headcount I am struggling with: most examples take headcount at end of period, or the average of headcount at start and end, but not the actual average per day headcount in a period.

 

I can do this in Power Query but we need end users to dynamically interact with the data - filter by Department, date ranges, etc.

 

The data is as you'd expect - a fact table of employee IDs with from and to dates plus a few details.

 

Dummy data including (currently botched attempts at) measures:

 

https://1drv.ms/u/s!AmzInrHWVr2zgYU0akVhd-ilLdCp-A

 

thanks for any help !

Need Help
Tips and Tricks
  • TomTomTom's avatar
    TomTomTom
    5 years ago

    Thank you both for your ideas.

     

    I am fairly sure I have cracked it with these three measures (the names are with the real data set, not the dummy one):

    Leavers =

    VAR MaxDate = Max ( Date_Table[Date] )
    VAR MinDate = Min ( Date_Table[Date] )

    RETURN
    0 +
    CALCULATE (
    COUNTROWS(Raw_Data),
    Raw_Data[To] <= MaxDate,
    Raw_Data[To] >= MinDate,
    All(Date_Table)
    )
     
     
     
     
    Headcount =

    VAR MaxDate = Max ( Date_Table[Date] )
    VAR MinDate = Min ( Date_Table[Date] )

    RETURN
    0 +
    CALCULATE (
    COUNTROWS(Raw_Data),
    Raw_Data[From] <= MaxDate,
    Raw_Data[To] >= MinDate || ISBLANK(Raw_Data[To]),
    All(Date_Table)
    )
     
     
     
     
    Av_Turnver =

    VAR MaxDate = Max(Date_Table[Date])
    VAR MinDate = Min(Date_Table[Date])
    VAR DayCount = 1 + (MaxDate - MinDate)

    RETURN

    DIVIDE (
    [Leavers] * DayCount,
    SUMX(Date_Table, [Headcount]),
    0
    )
     
     
     

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Please try these two measure expressions.  Use the 2nd one in your visual with Year and Month columns.

     

    Turnover =
    VAR MaxDate =
        MAX ( Calendar[Date] )
    VAR MinDate =
        MIN ( Calendar[Date] )
    VAR vCurrent =
        CALCULATE (
            COUNTROWS ( Employee_Fact ),
            ALL ( 'Calendar'[Date] ),
            Employee_Fact[First_Day] <= MaxDate,
            Employee_Fact[Last_Day] >= MinDate
        )
    VAR vCurrentEmployees =
        DISTINCT ( Employee_Fact[Employee_ID] )
    VAR vPrevMonthEmployees =
        CALCULATETABLE (
            DISTINCT ( Employee_Fact[Employee_ID] ),
            PREVIOUSMONTH ( 'Calendar'[Date] )
        )
    VAR vLeavers =
        COUNTROWS (
            EXCEPT (
                vPrevMonthEmployees,
                vCurrentEmployees
            )
        )
    RETURN
        DIVIDE (
            vLeavers,
            vCurrent
        )
    Avg Monthly Turnover =
    AVERAGEX (
        SUMMARIZE (
            'Calendar',
            'Calendar'[Year],
            'Calendar'[Month]
        ),
        [Turnover]
    )

     

    Regards,

    Pat

    • TomTomTom's avatar
      TomTomTom
      Icon for Helper II rankHelper II
      5 years ago

      Thanks Pat.

       

      Possibly I am missing something, but I don't think this gives me average employees in a given period.

       

      For instance, if we employed:

       

      Day 1 - 1 employee       (doesn't leave)

      Day 2 - 997 employees  (996 all leave)

      Day 3 - 1 employee       (same as day 1 employee)

       

      then our average employee in this period is 333 and so our turnover is 996/333.

       

      Or have I misunderstood?

       

      thanks,

      Tom

  • TomTomTom's avatar
    TomTomTom
    Icon for Helper II rankHelper II
    5 years ago

    Thank you both for your ideas.

     

    I am fairly sure I have cracked it with these three measures (the names are with the real data set, not the dummy one):

    Leavers =

    VAR MaxDate = Max ( Date_Table[Date] )
    VAR MinDate = Min ( Date_Table[Date] )

    RETURN
    0 +
    CALCULATE (
    COUNTROWS(Raw_Data),
    Raw_Data[To] <= MaxDate,
    Raw_Data[To] >= MinDate,
    All(Date_Table)
    )
     
     
     
     
    Headcount =

    VAR MaxDate = Max ( Date_Table[Date] )
    VAR MinDate = Min ( Date_Table[Date] )

    RETURN
    0 +
    CALCULATE (
    COUNTROWS(Raw_Data),
    Raw_Data[From] <= MaxDate,
    Raw_Data[To] >= MinDate || ISBLANK(Raw_Data[To]),
    All(Date_Table)
    )
     
     
     
     
    Av_Turnver =

    VAR MaxDate = Max(Date_Table[Date])
    VAR MinDate = Min(Date_Table[Date])
    VAR DayCount = 1 + (MaxDate - MinDate)

    RETURN

    DIVIDE (
    [Leavers] * DayCount,
    SUMX(Date_Table, [Headcount]),
    0
    )
     
     
     
  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi TomTomTom ,

     

    I'm glad that you have solved the issue by yourself and  share it with us. Would you please consider Accept helpful reply as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai