Calculate Employee Retention

[Lucy64]

So my report is starting to look really good thanks to the help from this group.  Next step is to calculate Employee retention over a given period filtered by date.

So I need to calculate:

Number of employees starting the year =

Number of employees who started the year and finished the year =

I have a date table with:  Date; Year; Month

Personal table: HireDate; TermDate (TermDate is blank for Active employees)

I have already looked at other conversations on this topic but I could not get them to work for my project.  

[Anonymous · Accepted solution]

Hi @Lucy64,

For your requirement, you can take a look at below formulas.

Method A: use a new table to summary these records.

Table formula:

Table = DISTINCT(SELECTCOLUMNS('Date Table',"Year",[Year],"started and finished current year",COUNTX(FILTER(ALL(Sheet1),Sheet1[HireDate].[Year]=Sheet1[TermDate].[Year]&&Sheet1[HireDate].[Year]=EARLIER('Date Table'[Year])),[HireDate]),"Still emloyed",COUNTX(FILTER(ALL(Sheet1),Sheet1[TermDate]=BLANK()&&Sheet1[HireDate].[Year]=EARLIER('Date Table'[Year])),[HireDate])))

Notice: Date table is a calendar table with date, month, year; sheet1 is the table which has HireDate and TermDate.

Date Table = ADDCOLUMNS(CALENDAR(MIN(Sheet1[HireDate]),MAX(Sheet1[TermDate])),"Year",YEAR([Date]),"Month",MONTH([Date]))

Method B: use measures.

Measure formula:

started and finished this year = COUNTX(FILTER(ALL(Sheet1),Sheet1[HireDate].[Year]=Sheet1[TermDate].[Year]&&Sheet1[HireDate].[Year]=MAX(Sheet1[HireDate].[Year])),Sheet1[HireDate])

Still Employed = COUNTX(FILTER(ALL(Sheet1),Sheet1[TermDate]=BLANK()&&Sheet1[HireDate].[Year]=MAX(Sheet1[HireDate].[Year])),[HireDate])

Table visual:

Regards,

Xiaoxin Sheng

[Vvelarde · Accepted solution]

hi @Lucy64

Try these measures,

StartingYear

Employees_StartingYear =
VAR countemployees =
    CALCULATE (
        COUNTROWS ( Personal ),
        FILTER (
            Personal,
            Personal[HireDate] <= FIRSTDATE ( DateTable[Date] )
                && OR (
                    YEAR ( Personal[TermDate] ) >= VALUES ( DateTable[Year] ),
                    Personal[TermDate] = BLANK ()
                )
        )
    )
RETURN
    IF ( countemployees > 0, countemployees, 0 )

StartingandFinishingYear

Employees_Starting_FinishingYear =
VAR countemployees =
    CALCULATE (
        COUNTROWS ( Personal ),
        FILTER (
            Personal,
            AND (
                Personal[HireDate] <= FIRSTDATE ( DateTable[Date] )
                    && OR (
                        YEAR ( Personal[TermDate] ) >= VALUES ( DateTable[Year] ),
                        Personal[TermDate] = BLANK ()
                    ),
                OR (
                    Personal[TermDate] >= LASTDATE ( DateTable[Date] ),
                    Personal[TermDate] = BLANK ()
                )
            )
        )
    )
RETURN
    IF ( countemployees > 0, countemployees, 0 )

[Anonymous · Accepted solution]

Hi @Lucy64,

For your requirement, you can take a look at below formulas.

Method A: use a new table to summary these records.

Table formula:

Table = DISTINCT(SELECTCOLUMNS('Date Table',"Year",[Year],"started and finished current year",COUNTX(FILTER(ALL(Sheet1),Sheet1[HireDate].[Year]=Sheet1[TermDate].[Year]&&Sheet1[HireDate].[Year]=EARLIER('Date Table'[Year])),[HireDate]),"Still emloyed",COUNTX(FILTER(ALL(Sheet1),Sheet1[TermDate]=BLANK()&&Sheet1[HireDate].[Year]=EARLIER('Date Table'[Year])),[HireDate])))

Notice: Date table is a calendar table with date, month, year; sheet1 is the table which has HireDate and TermDate.

Date Table = ADDCOLUMNS(CALENDAR(MIN(Sheet1[HireDate]),MAX(Sheet1[TermDate])),"Year",YEAR([Date]),"Month",MONTH([Date]))

Method B: use measures.

Measure formula:

started and finished this year = COUNTX(FILTER(ALL(Sheet1),Sheet1[HireDate].[Year]=Sheet1[TermDate].[Year]&&Sheet1[HireDate].[Year]=MAX(Sheet1[HireDate].[Year])),Sheet1[HireDate])

Still Employed = COUNTX(FILTER(ALL(Sheet1),Sheet1[TermDate]=BLANK()&&Sheet1[HireDate].[Year]=MAX(Sheet1[HireDate].[Year])),[HireDate])

Table visual:

Regards,

Xiaoxin Sheng

[Lucy64]

So I have the Number of employees starting the year: (total at end of previous year)

Number of Employees PY =

CALCULATE (

    [Balance],

    FILTER ( ALL ( DATES ), DATEADD(DATES[Date], 1, YEAR) <= MAX ( DATES[Date] ) )

)

Now I need to work out which of these empolyees are still emloyed at the end of the year!  So needs to check that the TermDate is Blank or greater than the end date of the filter.

[blopez11]

If you want to find those who left in the same year they started, can you create a calculated column and set it to a 1 if the start year and term year are the same, otherwise set it to 0?

Then you can create a measure that takes the difference between the sum of those that started in a given year, and the sum of those that left the same year they started, for the given year (i.e. new calculated column)