Cohort analysis - retention rate

[Anonymous]

Hi, I'm new to Power BI and I want to calculate and show retention rate on dashboard.

I have tables regarding user cohort data and we calculate retention rate as below.

ex.

Retention Rate for Android JP user who installed App A through Facebook network, campaign_1, Image_1 during 2020-01-01~2020-01-02

- Retention Rate (D+1) = (50+140)/(100+200)

Retention Rate for iOS JP user who installed App A through Google network, campaign_2, Video_1 during 2020-01-01~2020-01-02

- Retention Rate (D+1) = (30+40)/(50+100)

* Retention Rate (D+n) = retained_users with period n / retained_users with period 0

App	OS	Country	Network	Campaign	Ad Group	Creative	Date	Period	Retained_users
A	Android	JP	Facebook	campaign_1	Image	Image_1	2020-01-01	0	100
A	Android	JP	Facebook	campaign_1	Image	Image_1	2020-01-01	1	50
A	Android	JP	Facebook	campaign_1	Image	Image_1	2020-01-02	0	200
A	Android	JP	Facebook	campaign_1	Image	Image_1	2020-01-02	1	140
A	iOS	JP	Google	campaign_2	Video	Video_1	2020-01-01	0	50
A	iOS	JP	Google	campaign_2	Video	Video_1	2020-01-01	1	30
A	iOS	JP	Google	campaign_2	Video	Video_1	2020-01-02	0	100
A	iOS	JP	Google	campaign_2	Video	Video_1	2020-01-02	1	40

I want to show retention rate by variables (App, OS, Country, Network, Campaign, Ad Group, Creative, Date).

How can I solve this problem ?

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[Johann1978]

Thank you in advance for your time and guidance..I am more than happy to attach a file the info below isn't clear. 

The formula for the Output is below

Month 3 Sum( Month active Column: rows 0-3 Jan-Jan 2021)/Total Column :Jan-Mar 2021

Month 5 Sum (Month Active Column: rows 0-5 Jan-Nov)/Total Column :Jan-Nov

DATA TABLE

0	4,103	3,674	3,858	4,015	4,391	5,985	4,420	5,390	5,801	2,905	2,880	3,722	3,421	3,789	4,948	2,885
1	2,575	1,917	2,072	2,729	3,067	3,803	2,794	4,169	4,221	2,013	1,993	2,535	2,518	3,598	3,505
2	1,404	961	1,207	1,324	1,560	2,095	1,593	1,648	1,939	969	1,014	1,759	1,190	1,204
3	1,307	967	1,048	1,414	2,042	2,375	1,963	1,674	1,662	1,247	1,126	1,369	1,276
4	846	680	957	1,396	1,605	1,891	1,334	1,513	1,964	924	899	1,198
5	780	1,033	1,186	1,117	1,257	1,449	948	1,199	1,206	739	689
6	1,063	987	838	876	1,044	1,173	944	879	1,000	560
TOTAL	46,798	39,759	42,528	42,541	52,166	63,271	49,469	56,152	59,594	34,984	34,898	49,548	41,968	43,666	58,532	36,388
OUTPUT
	Attrition
Same Month (0)	8.8%
M+1	14.9%
M+2	17.9%
M+3	21.0%
M+4	23.7%
M+5	26.2%
M+6	28.2%

[Johann1978]

Sorry for the view above it trimmed out the month headers on the columns when it converted it. I am more than happy to send an email if you have time

[lbendlin · Accepted solution]

You cannot show the retention rate by date.  Remove the [date] column from your table visual.

Then it is easy to create the measure. For each "row" of the table visual identify if the period is 0. If yes, skip the row (return BLANK() or 1). If no, create a variable to calculate the sum of Retained_users , replacing the Period filter with 0. Lastly divide the sum of Retained_users for the current "row" by the calculated variable.

[Johann1978]

@lbendlin lbendlin

I am wondering if you could help me with a similar issue around attrition rate. I have the data setup in a way that has the periods and the churn for those periods identified.

What I am struggling with is how to get the cummulative totals. (See examples from Excel)

I have spent a week wracking my brain on the caluclation to perform this, but I haven't been succesful.

Period 3 Example

Period 6 Example

I was trying to do a date diff based on the max date where the subscriber count wasn't blank, but I can't get it accross the finish line. I would GREATLY appreciate anyone who could help