Calculate Employee Retention

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

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 )