Retention Rate Measures

[Anonymous]

Hello,

I am really struggling to create a report on retention. Looking for some support and guidance from the community.

To set the scene: the data in question is from a membership organisation, where memberships last for a period of 12 months.

I've provided a link to the dataset below. The dataset represents various transactions by distinct customers (customer_id), including various fields such as member's original 'joindate' and the 'expirydate' of their membership, along with other transactional fields. The dataset covers the time period 1st January 2019 - 30th September 2019.

I would like to create measures for retention over a rolling 12 month period, showing the % retention rate and the number of members who have renewed their annual membership. Having said that, there is one additional parameter to include: the membership organisation allows an additional 90 day grace period / buffer period post-expiry date for the member to renew, before the membership is considered as lapsed. I would like this factored into the retention rate measure too and to see the number of lapsed memberships (customer_id).

I'm picturing this in a matrix table. As you'll see attached, I've set this up to show the measures split by 'default_billing_region_ENGLISH'.

As this is retention we're talking about, clearly the measures/calculations must ignore new members with a 'joindate' that falls within the period in question. 

Can anyone help please? 

Many thanks for your time and support.

Raw Data 

Power BI File 

[fhill]

If the above solution didn't resolve your question, can you provide some further details please.  Here's 4 Random customers I've pulled out of the file.  Can you go into more details on how these member's expiration data factors into your 12 month calculation?  Where would these 4 customers fall in your retention % calculation?

607	1990/01/01	2020/09/30	2019/02/01 12:38:44
607	1990/01/01	2020/09/30	2019/02/01 12:38:44
607	1990/01/01	2020/09/30	2019/04/24 23:21:25
607	1990/01/01	2020/09/30	2019/08/29 11:22:48
621	2006/07/03	2019/09/30	2019/05/04 19:13:09
621	2006/07/03	2019/09/30	2019/05/04 19:13:09
621	2006/07/03	2019/09/30	2019/05/04 19:13:09
621	2006/07/03	2019/09/30	2019/05/05 0:33:14
621	2006/07/03	2019/09/30	2019/05/05 0:33:14
625	2010/12/07	2020/09/30	2019/08/01 23:52:20
630	2014/01/01	2222/09/30	2019/01/01 0:28:24
630	2014/01/01	2222/09/30	2019/01/01 1:08:30
630	2014/01/01	2222/09/30	2019/01/26 2:52:09
630	2014/01/01	2222/09/30	2019/01/29 5:22:08
630	2014/01/01	2222/09/30	2019/01/29 5:39:00
630	2014/01/01	2222/09/30	2019/01/29 6:09:01
630	2014/01/01	2222/09/30	2019/02/08 4:54:03
630	2014/01/01	2222/09/30	2019/02/22 12:01:28
630	2014/01/01	2222/09/30	2019/06/21 8:46:02

[v-eachen-msft]

Hi @Anonymous ,

You could use ALLSELECTED(DateKey[Date]) to get date range.

Then calculate the "id" count( with COUNTROWS() and DISTINCT() functions ) where "expirydate" is greater than your date range and divide with total. Here is the Retention Rate.

Calculate the "id" count where "expirydate" is below ALLSELECTED(DateKey[Date]). Here is the the number of lapsed memberships.