Forum Discussion
Dynamic Retention Calculation
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% |