calculate drop out rate members

[emillio]

Hi there, I am trying to calculate the drop out rate of our memberships by number of months. For example: I have 10 members who joined in month 1, nobody left. Drop out rate is 0% for month 1. Now in month 2, there are 3 members leaving, however, 7 are still a member in this month. Drop out rate should be 30% for month 2.

Can anybody help me with the right way of calculating this. I have a start date field and an end date field. Thanks so much! 

[CahabaData]

provide a handful of table records/rows to display precisely how the data is stored in the table.  That is needed to correctly define the dax statement.

[emillio]

So here's an example. With a dateiff function I could retreive the # of months between a start and end date. However, I now need to calculate the percentage of drop out's amongst the total number of starting members. In the example below 13 members started in november, of whom 5 dropped out in the first month. Drop out rate for month 1 should be 38%

[v-yuezhe-msft]

Hi @emillio,

What is the logic do you use to calculate drop rate for month 12 as new members are joined in December.

For drop rate of month 11, you can create the following measures.

Incount = CALCULATE(COUNT(Table1[Client_ID]))
dropoutcount = CALCULATE(COUNT(Table1[Client_ID]), FILTER(Table1,Table1[Status]="inactive" && YEAR(Table1[End date])=YEAR(Table1[Start Date]) && MONTH(Table1[End date])=MONTH(Table1[Start Date])))
drop rate = [dropoutcount]/[Incount]

Thanks,
Lydia Zhang

[emillio]

Thanks Lydia, the code I'm using now to calculate the months someone has been active is the following:

months active = if(Blad1[Status]="Inactive";DATEDIFF(Blad1[Start date];Blad1[End date];MONTH);DATEDIFF(Blad1[Start date];TODAY();MONTH))

The below is an example of what the outcome could look like. I load the above calculated column as rows in my pivot and count the % of members that have dropped out against all members that have "passed" that month. So essentially, all members that are still active in month 2, 3, and so and so forth, should be set against the total # of members that dropped out in month 1. Still following me?

Thanks for your help.

[v-yuezhe-msft]

Hi @emillio,

How do you calculate the dropout rate for December 2016? Assume that three new members joined in December 2016, and one member dopped out in December 2016, do you want to calculate the dropout rate for December 2016 using :1/((3-1)+(13-5)) or 1/13?

Thanks,
Lydia Zhang

[emillio]

Hi there, 

To simplify for now I am wanting to look at the number of months active, e.g. drop out rate for members active 1 month, 2 months, etc. Any ideas there ? 

Thanks!

[v-yuezhe-msft]

Hi @emillio,

Please help to share all data of your table and post expected result.

Thanks,
Lydia Zhang

[Anonymous]

Hi,

I was after some assistance with working out cumulative drop out or during a period of time.  e.g. 8812 were recruited in Jan 2015 and I would like to know how many have left after each month until today's date.

I have created the following formulas which gives me the drop out rate by calculating the number of people who have started 8812 divided the amount of people who have left in total since until today's date.

IncountNO = CALCULATE(COUNT('Norge faddere'[Kundenummer (Kontakt)])) /IncountperenddateNO = CALCULATE(COUNT('Norge faddere'[Kundenummer (Kontakt)]), Filter ('Norge faddere', 'Norge faddere'[Avslutningsdato])) to get the drop out rate which works however  I would like to display this onto a graph for a series of different periods.

I feel that my formula is lacking a time range in order for this to be achieved like a dax pattern.  I have also tried your formula without success.

What I would like to achieve is below without all the filtering that is required in the dashboard as I will need to apply this to many different timeframes.

This is what I havw achieved just for jan 2015. The dashboard is filtered to  Date start filtered to 2015.

 

So I would like to be able to apply the drop out formula along the the entire series.  737/8812, 3826/8812 and so on. without all the filtering required to achieve just this one graph.

I really hope this possible and that I have made myself understandable.

I would really appreaciate some help on this.

Thank you,

Maria

[Anonymous]

Hi,

I was after some assistance with working out cumulative drop out or during a period of time.  e.g. 8812 were recruited in Jan 2015 and I would like to know how many have left after each month until today's date.

I have created the following formulas which gives me the drop out rate by calculating the number of people who have started 8812 divided the amount of people who have left in total since until today's date.

IncountNO = CALCULATE(COUNT('Norge faddere'[Kundenummer (Kontakt)])) /IncountperenddateNO = CALCULATE(COUNT('Norge faddere'[Kundenummer (Kontakt)]), Filter ('Norge faddere', 'Norge faddere'[Avslutningsdato])) to get the drop out rate which works however  I would like to display this onto a graph for a series of different periods.

I feel that my formula is lacking a time range in order for this to be achieved like a dax pattern.  I have also tried your formula without success.

What I would like to achieve is below without all the filtering that is required in the dashboard as I will need to apply this to many different timeframes.

This is what I havw achieved just for jan 2015. The dashboard is filtered to  Date start filtered to 2015.

 

So I would like to be able to apply the drop out formula along the the entire series.  737/8812, 3826/8812 and so on. without all the filtering required to achieve just this one graph.

I really hope this possible and that I have made myself understandable.

I would really appreaciate some help on this.

Thank you,

Maria

[dkay84_PowerBI]

Are you trying to display the retention by months active, or by month (i.e. Jan, Feb, March etc.)?

[emillio]

By month as we have new members starting every month