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...
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

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
Super 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