DAX measure to calculate membership retention rate

DAX DAX Calculations
1

I have a Membership table with with Start_Date and End_Date. I have created a Date table and mapped Date to Start_Date. I have a Date slicer

How can I calculate membership retention rate for the selected year?

I tried below DAX query which is not returning any value.

Benenden.pbix (113.9 KB)

RR2 = 
VAR SelectedYear = YEAR(SELECTEDVALUE('Date'[Date]))
VAR PriorYearMembers = CALCULATE(
    DISTINCTCOUNT(MEMBERSHIP[REF_NUM]),
    USERELATIONSHIP(MEMBERSHIP[START_DATE], 'Date'[Date]),
    YEAR(MEMBERSHIP[START_DATE]) = SelectedYear - 1
)
VAR AllSelectedYearMembers = CALCULATE(
    DISTINCTCOUNT(MEMBERSHIP[REF_NUM]),
    USERELATIONSHIP(MEMBERSHIP[START_DATE], 'Date'[Date]),
    YEAR(MEMBERSHIP[START_DATE]) = SelectedYear
)
VAR RetainedMembers = CALCULATE(
    DISTINCTCOUNT(MEMBERSHIP[REF_NUM]),
    USERELATIONSHIP(MEMBERSHIP[START_DATE], 'Date'[Date]),
    FILTER(
        VALUES(MEMBERSHIP[END_DATE]),
        CONTAINS(
            CALCULATETABLE(
                VALUES(MEMBERSHIP[REF_NUM]),
                USERELATIONSHIP(MEMBERSHIP[START_DATE], 'Date'[Date]),
                YEAR(MEMBERSHIP[START_DATE]) = SelectedYear - 1
            ),
            MEMBERSHIP[REF_NUM],
            MEMBERSHIP[END_DATE]
        )
    )
)
RETURN DIVIDE(RetainedMembers, PriorYearMembers + AllSelectedYearMembers - PriorYearMembers, 0)
2

For 2022 retention I have used following DAX query. Kindly suggest a query to calculate for selected year.

Retention Rate 2022 = 
DIVIDE(
    COUNTROWS(
        FILTER(
            'MEMBERSHIP',
            'MEMBERSHIP'[END_DATE]>= DATE(2022,1,1) 
            && 'MEMBERSHIP'[END_DATE] < DATE(2023,1,1)
        )
    ),
    COUNTROWS(
        FILTER(
            'MEMBERSHIP',
            'MEMBERSHIP'[START_DATE]< DATE(2022,1,1)
            && (
                'MEMBERSHIP'[END_DATE] >= DATE(2022,1,1) 
                || ISBLANK('MEMBERSHIP'[END_DATE])
            )
        )
    )
)
3

Bumping this post for more visibility from our experts and users.

HI @AnilKumarPoda

While waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!

4

Hi @AnilKumarPoda

Due to inactivity, we’d like to conclude that your inquiry was out of the experts and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

5

Below code worked for me as intended with slicer selection

RR = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
CALCULATE(
DIVIDE(
    COUNTROWS(
        FILTER(
            Membership,
            Membership[End_Date] >= DATE(SelectedYear, 1, 1) 
            && Membership[End_Date] < DATE(SelectedYear + 1, 1, 1)
        )
    ),
    COUNTROWS(
        FILTER(
            Membership,
            Membership[Start_Date] < DATE(SelectedYear, 1, 1)
            && (
                Membership[End_Date] >= DATE(SelectedYear, 1, 1) 
                || ISBLANK(Membership[End_Date])
            )
        )
    )
), ALL('Date'))