Member retention

[Trengroove]

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

[Eric_Zhang · Accepted solution]

---

@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]

 

[ike1860]

What about month over month member retention?

[Ashish_Mathur]

Hi @ike1860,

Share a dataset and show the expected result.

[ike1860]

In the terminated date column 12/31/1899 is the default date value entered by our database when the date is not entered.

I'm trying to calculate and show monthly retention either as a cohort or best visual. 

Thanks!

[Ashish_Mathur]

Hi,

Share the link from where i can download your sample data.  In a simple Table, please show the expected result.

[ike1860]

---

wrote:

Hi,

Share the link from where i can download your sample data.  In a simple Table, please show the expected result.

Below is a link to sample data

---

Retention Sample Data 

---

---

[Ashish_Mathur]

Hi,

On another tab in the same workbook, in a simple table, show your expected result.

[ike1860]

---

wrote:

Hi,

On another tab in the same workbook, in a simple table, show your expected result.

---

Here you go 

https://ymcaspbc-my.sharepoint.com/:x:/g/personal/ichimbandi_ymcaspbc_org/EeXGYOgWBCNFlQ-ipqcWU4kBWc...

[Ashish_Mathur]

Hi,

I cannot download the file from there.

[ike1860]

Sorry about that. Try this one.

https://ymcaspbc-my.sharepoint.com/:x:/g/personal/ichimbandi_ymcaspbc_org/EeXGYOgWBCNFlQ-ipqcWU4kBWc...

[Ashish_Mathur]

Hi,

As requested in my previous message, please show the expected result on sheet2.

[ike1860]

I did. I’m trying to calculate retention on a month to month basis.

[Ashish_Mathur]

Sheet 2 just has column headers.  The values in columns are blank.  Please tell me exactly what numbers you are expecting there so that i can verify my solution.

[GilbertQ]

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.

https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...