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

[TomTomTom]

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 !

[v-deddai1-msft]

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

[TomTomTom · Accepted solution]

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

)

[amitchandak]

@TomTomTom , At the first look at data, seems like it this blog should help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://www.youtube.com/watch?v=e6Y-l_JtCq4

[mahoneypat]

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]

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