Calculating employee turnover at top-level and department level

Question

I ran across a great post on calculating employee turnover rates (https://finance-bi.com/power-bi-employee-turnover-rate/). Unfortunately, my data is not quite like this (isn't that always the case?). My data includes positions and statuses with dates for each. There is an effective date range for each record. It would look like this (using the same data as the website above):

Employee Start Date	Employee End Date	Position ID	Position Start Date	Position End Date	Department	Status	Effective Start Date	Effective End Date	Employee ID
2018-02-03	2019-09-05	Jo01	2018-02-03	2018-08-31	Dept1	PT	2018-02-03	2018-03-15	John
2018-02-03	2019-09-05	Jo01	2018-02-03	2018-08-31	Dept1	FT	2018-03-16	2018-08-31	John
2018-02-03	2019-09-05	Jo02	2018-09-01	2019-09-05	Dept2	FT	2018-09-01	2019-09-05	John
2018-05-07	2018-10-10	Al01	2018-05-07	2018-10-10	Dept1	PT	2018-05-07	2018-05-31	Alan
2018-05-07	2018-10-10	Al01	2018-05-07	2018-10-10	Dept1	FT	2018-06-01	2018-10-10	Alan
2019-01-08	2019-05-09	Li01	2019-01-08	2019-05-09	Dept2	FT	2019-01-08	2019-05-09	Liz
2018-05-05	9999-12-31	Ma01	2018-05-05	2018-12-31	Dept3	PT	2018-05-05	2018-09-30	Matt
2018-05-05	9999-12-31	Ma01	2018-05-05	2018-12-31	Dept3	FT	2018-10-01	2018-12-31	Matt
2018-05-05	9999-12-31	Ma02	2019-01-01	9999-12-31	Dept2	PT	2019-01-01	9999-12-31	Matt
2019-03-02	9999-12-31	An01	2019-03-02	9999-12-31	Dept2	FT	2019-03-02	9999-12-31	Ann
2019-05-07	9999-12-31	Sa01	2019-05-07	9999-12-31	Dept3	FT	2019-05-07	9999-12-31	Samantha

At the top level, the turnover numbers would still the same. But now we could calculate the department turnover as well. Any thoughts on how to do that? Or even just how to calculate the top level turnover rate given this data?

EDIT: 2021-05-24 These turnover rates are calculated looking back 12 months. The user should be able to select the ending date of the data on the report. I have a separate date table that is currently disconnected.

Here is what the output tables would look like in a visualization (showing the user selected Dec 2019):

Entire Organization (from website above)

Date	Employee Count	Terminations	Turnover %
Dec 2019	3	2	80%
Nov 2019	3	2	80%
Oct 2019	3	2	80%
Sep 2019	3	3	100%
Aug 2019	4	2	57%
Jul 2019	4	2	57%
Jun 2019	4	2	57%
May 2019	4	1	57%
Apr 2019	4	1	40%
Mar 2019	4	1	40%
Feb 2019	3	1	50%
Jan 2019	3	1	67%

Dept2

Date	Employee Count	Terminations	Turnover %
Dec 2019	2	2	133%
Nov 2019	2	2	133%
Oct 2019	2	2	133%
Sep 2019	3	1	50%
Aug 2019	3	1	67%
Jul 2019	3	1	67%
Jun 2019	3	1	67%
May 2019	4	0	0%
Apr 2019	4	0	0%
Mar 2019	4	0	0%
Feb 2019	3	0	0%
Jan 2019	3	0	0%

END EDIT: 2021-05-24

Any help would be greatly appreciated. I have been trying everything I can think of but I am coming up with nothing.

Thanks!

dmonder dmonder · Accepted Answer · 2021-05-26T15:42:03

I figured this out. First, I created a base measure to count distinct employees:

# Distinct Employees = DISTINCTCOUNT( Table1[Employee ID] )

Then, I created 2 measures to count the number of "current" employees and the number of employees 12 months ago (this was the easy part):

# Employees Working as of Date = 
    VAR CYEndDate = MAX( Dates[Full_Date] )
    RETURN
        CALCULATE(
            [# Distinct Employees],
            Table1[Effective Start Date] <= CYEndDate && Table1[Effective End Date] >= CYEndDate
        )

# Employees Working as of Date 12 months ago = 
    VAR PYEndDate = LASTDATE( DATEADD( Dates[Full_Date], -1, YEAR ) )
    RETURN
        CALCULATE(
            [# Distinct Employees],
            Table1[Effective Start Date] <= PYEndDate && Table1[Effective End Date] >= PYEndDate
        )

Now, the part I had most trouble with was to create the measure for the termed employees over the past 12 months. I needed to be able to filter the HR table to include only those records that match the filters before checking to see if they termed. Here is the measure I created that seems to work:

# Employees Termed as of Date over 12 months = 
VAR CYEndDate = MAX( Dates[Full_Date] )
VAR PYEndDate = LASTDATE( DATEADD( Dates[Full_Date], -1, YEAR ) )

VAR PersonMaxRecordID = 
    ADDCOLUMNS(
        GROUPBY(
            FILTER( 
                Table1,
                Table1[Effective Start Date] <= CYEndDate && Table1[Effective End Date] >= PYEndDate
            ),
            Table1[Campus ID]
        ),
        "MaxRecordID", CALCULATE( MAX( Table1[Record ID] ) )
    )
    RETURN
        CALCULATE(
            [# Distinct Employees],
            TREATAS( PersonMaxRecordID, Table1[Campus ID], Table1[Record ID] ),
            Table1[Effective End Date] <= CYEndDate
        )

Now I have a report that I can filter by department, status, and any number of other attributes that I have available on my employees. I got this idea from another Stack Overflow post's answer Dax filtering expressions, last date of every day.

I hope this is helpful for others.

Calculating employee turnover at top-level and department level

1 Answers