Forum Discussion

Lucy64's avatar
Lucy64
Advocate II
9 years ago
Solved

Calculate Employee Retention

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.  

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
    Vvelarde
    9 years ago

    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 )

4 Replies

  • Lucy64's avatar
    Lucy64
    Advocate II
    9 years ago

    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's avatar
      blopez11
      Super User
      9 years ago

      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)

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
    Vvelarde
    Community Champion
    9 years ago

    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 )