retention rate

[benz-fsproduce]

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.

[amitchandak]

@benz-fsproduce , remove the condition on today and try.

if not share formula in text format

[benz-fsproduce]

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 · Accepted solution]

@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