Member retention

---

Trengroove wrote:

Hi All,

 

I am trying to put together a report that shows member retention year on year. I hoping to make this spliceable by a range of other variables in the data.

 

The data is formed at the end of each month and simply lists all members with their ID number for all months. Is there a way to compare the members in a given month and determine what percentage were members 12 months prior? ie: proportion of members in April 2017 who were also members in April 2016?

 

Ideally I would be able to choose the year end month that I want to measure retention for, but maybe that's not possible.

Here is a sample of the data.

 

I am still relatively new to Powerbi, so any help would be greatly appreciated.

 

Thanks

---

Trengroove

With the calendar table from GilbertQ, you can create 3 measures as below. See more details in the attached pbix.

Member in Previous 12th Month =
CALCULATE (
    DISTINCTCOUNT ( 'yourTable'[MemberID] ),
    SAMEPERIODLASTYEAR ( dimdate[Date] )
)

members retained =
VAR membersInPrevious12Month =
    CALCULATETABLE ( 'yourTable', SAMEPERIODLASTYEAR ( dimdate[Date] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'yourTable'[MemberID] ),
        FILTER (
            'yourTable',
            CONTAINS (
                membersInPrevious12Month,
                'yourTable'[MemberID], 'yourTable'[MemberID]
            )
        )
    )

retention rate = [members retained]/[Member in Previous 12th Month]