topic Re: Dynamic Retention Calculation in DAX Commands and Tips

Dynamic Retention Calculation

Johann1978 — Thu, 15 Jul 2021 23:04:46 GMT

Hoping someone can help. I have been working to convert this excel table into dax calculations in Power BI, but have hit a bit of a wall. The calculation looks at the number users created by month and the number who churn by the number of months active. I am hoping someone might have created similar views in BI that I can leverage the code.

Thanks in advance,

Below are a few screen shots of the dataset in Excel.

	Retention
Same Month	8.9%
M+1	15.1%
M+2	18.1%
M+3	21.4%
M+4	24.1%
M+5	26.4%
M+6	28.5%
M+7	30.3%
M+8	31.7%
M+9	33.2%
M+10	34.7%
M+11	36.1%
M+12	37.4%
M+13	38.4%
M+14	38.2%
M+15	38.6%

Re: Dynamic Retention Calculation

amitchandak — Fri, 16 Jul 2021 04:16:21 GMT

Johann1978 , check for the last measure in the blog, should be same as what you need

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/ba-p/1393410

Re: Dynamic Retention Calculation

Johann1978 — Thu, 22 Jul 2021 15:31:05 GMT

Hello,

So what I am struggling with is the cummulative aspect of the calculation. I'm sure someone will have an easy way to do this. The table below is similar to above just looking at attrition instea of retention.

The second table shows the cummulative attrition by vintage and is calculated by the formula below. I just can't figure out how to translate it into DAX

Month+2 =SUM(Rows M0-M2 columns Jan (2020-Feb2021)/Sum(row Total Columns Jan 2020-Feb2021)

Month+5 =Sum (Rows M0-M5 columns Jan (2020-Nov 2020))/Sum(Total Columns Jan 2020-Nov2020)

Its essentially looking for the last amount for each vintage and summing all of the values for each vintage that are less than or equal to that month and dividing that by the total amounts for the max month for that vintage.

Months Active	January	February	March	April	May	June	July	August	September	October	November	December	January	February	March	April
Currently Active	29,435	25,395	27,157	26,068	33,425	41,098	33,402	38,271	40,982	25,627	26,297	38,965	33,563	35,075	50,079	33,503
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
	40,450	34,627	37,485	38,063	47,347	58,696	46,454	53,864	57,775	34,424	34,898	49,548	41,968	43,666	58,532	36,388

	Attrition
Same Month	8.8%
M+1	14.9%
M+2	17.9%
M+3	21.0%
M+4	23.7%
M+5	26.2%