Forum Discussion
Dynamic Retention Calculation
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% |
2 Replies
Johann1978 , check for the last measure in the blog, should be same as what you need
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%
