Employee Turnover and Retention Dax

[Sam_BI_Analyst]

Hello Community,

I need some guidance/help with calculating Employee Retention and turnover using DAX

Here's my data looks like - 

I will attach this data below.

I also created a Date table using the formula -> Date = CALENDAR( DATE(1,1,2016), DATE(12,31,2024))

My Requirement - 

I need two tables (one for turnover and one for retention) with all the years from 2016 to 2024 in power bi not in excel!!!

Note - While calculating starting headcount and ending headcount please include rehire as a record for counts, dont skip rehires.

Thanks in advance.

Data - 

Employee ID	Company	Hire Date	Termination Date	Impact Reason
101	Google	7/4/2018		New Hire
101	Google		8/8/2020	Adv Oppurtunities
101	Google	10/8/2021		Re Hire
102	Facebook	3/15/2017		New Hire
102	Facebook		6/21/2021	Health
103	Facebook	2/14/2019		New Hire
104	Google	8/12/2018		New Hire
104	Google		5/16/2020	Child Care
105	Google	10/5/2018		New Hire
105	Google		12/20/2018	Adv Oppurtunities
105	Google	10/20/2021		Re Hire
105	Google		5/12/2022	Adv Oppurtunities
105	Google	2/12/2023		Re Hire

[rajendraongole1 · Accepted solution]

Hi @Sam_BI_Analyst -Hope you already have a relationship with Date table to Employee Table.

Create calculates the number of employees at the beginning of each year.

Starting Headcount =

VAR StartDate = MIN('Date'[Date])

RETURN

CALCULATE(

    COUNTROWS(Employee),

    FILTER(

        Employee,

        (Employee[Hire Date] <= StartDate &&

        (ISBLANK(Employee[Termination Date]) || Employee[Termination Date] > StartDate)) ||

        (Employee[Impact Reason] = "Re Hire" && Employee[Hire Date] <= StartDate)

    )

)

create another measure calculates the number of employees at the end of each year

Ending Headcount =

VAR EndDate = MAX('Date'[Date])

RETURN

CALCULATE(

    COUNTROWS(Employee),

    FILTER(

        Employee,

        (Employee[Hire Date] <= EndDate &&

        (ISBLANK(Employee[Termination Date]) || Employee[Termination Date] > EndDate)) ||

        (Employee[Impact Reason] = "Re Hire" && Employee[Hire Date] <= EndDate)

    )

)

Now create a turn over

Turnover =

CALCULATE(

    COUNTROWS(Employee),

    FILTER(

        Employee,

        Employee[Termination Date] >= MIN('Date'[Date]) &&

        Employee[Termination Date] <= MAX('Date'[Date])

    )

)

Retention Table =
SUMMARIZE(
'Date',
'Date'[Year],
"Starting Headcount", [Starting Headcount],
"Ending Headcount", [Ending Headcount],
"Retention Rate", DIVIDE([Ending Headcount], [Starting Headcount], 0)
)

create calculated tables as below for retention table:

Retention Table =

ADDCOLUMNS(

    VALUES('Date'[Year]),

    "Starting Headcount", [Starting Headcount],

    "Ending Headcount", [Ending Headcount],

    "Retention Rate", DIVIDE([Ending Headcount], [Starting Headcount], 0)

)

Turnover Table =
SUMMARIZE(
'Date',
'Date'[Year],
"Starting Headcount", [Starting Headcount],
"Turnover", [Turnover],
"Turnover Rate", DIVIDE([Turnover], [Starting Headcount], 0)
)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!