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 headco...
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
    )
     
     
     
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