Forum Discussion

benz-fsproduce's avatar
benz-fsproduce
Frequent Visitor
4 years ago
Solved

retention rate

Hi I am trying to calulate retention rate year by year. 

My current measure is:

 

 

 
 
It gives me retention rate, but it won't calculate for the current year. It works for previous years, but it will not for 2022. I know that it has something to do with the IF function. I would really appreciate some help. Thanks.
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    benz-fsproduce , I want you remove the if condition, as max of 2022 is > today 

     

    Retention Rate 2 =
    VAR _StartOfPeriod = MIN('Date'[Date])
    VAR _EndOfPeriod = MAX('Date'[Date])
    VAR _Result =
    IF( 1=1,
    VAR _EmployeesAtStart =
    CALCULATETABLE(
    VALUES(page[Employee Name]),
    page[Last Hire Date] <= _StartOfPeriod,
    page[Termination Date] >= _StartOfPeriod || ISBLANK(page[Termination Date])
    )
    VAR _EmployeesAtEnd =
    CALCULATETABLE(
    VALUES(page[Employee Name]),
    page[Last Hire Date] <= _EndOfPeriod,
    page[Termination Date] >= _EndOfPeriod || ISBLANK(page[Termination Date])
    )
    VAR _NoOFEmployeesAtStartAndEnd =
    COUNTROWS(
    INTERSECT(_EmployeesAtStart, _EmployeesAtEnd)
    )
    RETURN
    DIVIDE(_NoOFEmployeesAtStartAndEnd, COUNTROWS(_EmployeesAtStart))
    )
    RETURN
    _Result

3 Replies

    • benz-fsproduce's avatar
      benz-fsproduce
      Frequent Visitor
      4 years ago
      I tried removing "IF( TODAY() >= _EndOfPeriod," but it ended with an error. Here is my formula in text form.
       
      Retention Rate 2 =
      VAR _StartOfPeriod = MIN('Date'[Date])
      VAR _EndOfPeriod = MAX('Date'[Date])
      VAR _Result =
      IF( TODAY() >= _EndOfPeriod,
          VAR _EmployeesAtStart =
          CALCULATETABLE(
              VALUES(page[Employee Name]),
              page[Last Hire Date] <= _StartOfPeriod,
              page[Termination Date] >= _StartOfPeriod || ISBLANK(page[Termination Date])
          )
          VAR _EmployeesAtEnd =
          CALCULATETABLE(
              VALUES(page[Employee Name]),
              page[Last Hire Date] <= _EndOfPeriod,
              page[Termination Date] >= _EndOfPeriod || ISBLANK(page[Termination Date])
          )
          VAR _NoOFEmployeesAtStartAndEnd =
          COUNTROWS(
              INTERSECT(_EmployeesAtStart, _EmployeesAtEnd)
          )
          RETURN
              DIVIDE(_NoOFEmployeesAtStartAndEnd, COUNTROWS(_EmployeesAtStart))
      )
      RETURN
          _Result
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        benz-fsproduce , I want you remove the if condition, as max of 2022 is > today 

         

        Retention Rate 2 =
        VAR _StartOfPeriod = MIN('Date'[Date])
        VAR _EndOfPeriod = MAX('Date'[Date])
        VAR _Result =
        IF( 1=1,
        VAR _EmployeesAtStart =
        CALCULATETABLE(
        VALUES(page[Employee Name]),
        page[Last Hire Date] <= _StartOfPeriod,
        page[Termination Date] >= _StartOfPeriod || ISBLANK(page[Termination Date])
        )
        VAR _EmployeesAtEnd =
        CALCULATETABLE(
        VALUES(page[Employee Name]),
        page[Last Hire Date] <= _EndOfPeriod,
        page[Termination Date] >= _EndOfPeriod || ISBLANK(page[Termination Date])
        )
        VAR _NoOFEmployeesAtStartAndEnd =
        COUNTROWS(
        INTERSECT(_EmployeesAtStart, _EmployeesAtEnd)
        )
        RETURN
        DIVIDE(_NoOFEmployeesAtStartAndEnd, COUNTROWS(_EmployeesAtStart))
        )
        RETURN
        _Result