Forum Discussion
Member retention
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 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
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]
14 Replies
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
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]
What about month over month member retention?
Hi Trengroove
What you could do is to first create a Date table.This table will then allow you to use the native or build in Time Intelligence functions in Power BI.
For your example the DAX measure that you would want to create would be SAMEPERIODLASTYEAR, which would then go back to the previous period for last year.
In order to create the date table you could copy this blog post below.
