Hello,
I know there's many post on Retention Rate, but I cannot find anything on my problem.
I have 3 tables (+ a date table + a number 1-1000000 table)
| EngUID | CliUID | EngStartDate |
| 1 | 1 | 2023-11-20 |
| 2 | 2 | 2023-12-15 |
| 3 | 1 | 2024-01-15 |
| 4 | 1 | 2024-04-15 |
| 5 | 2 | 2024-04-15 |
| TrxUID | EngUID | TrxDate |
| 1 | 1 | 2023-11-20 |
| 2 | 2 | 2023-12-15 |
| 3 | 1 | 2023-12-15 |
| 4 | null | 2023-12-15 |
| 5 | 2 | 2024-01-15 |
| 6 | 3 | 2024-01-15 |
| 7 | 2 | 2024-02-15 |
| 8 | 2 | 2024-04-15 |
| 9 | 4 | 2024-04-15 |
| 10 | 5 | 2024-04-15 |
| 11 | 5 | 2024-05-15 |
I to end up with this Matrix
| EngStartDate/Month | 1 | 2 | 3 | 4 |
| 2023-11-20 | 100% | 100% | 100% | |
| 2023-12-15 | 100% | 100% | | 100% |
| 2024-01-15 | 100% | | | |
| 2024-04-15 | 100% | 50% | | |
This Idea is that I want to know from each date, how many EngUID (subscription) gave on month X. Basic stuff. I use
Var Payment_X_Done =
COUNT(Eng[EngUID]),
FILTER(Eng,Eng[NbVers]>= SELECTEDVALUE(Nb[count])) --Nb[count] is my number table
Return
CALCULATE(
Payment_X_Done/COUNT(Eng[EngUID])),
FILTER(eng,
Eng[NbVersMax]>=SELECTEDVALUE(Nb[Count]) --NbversMax is a calculated column
)
--NbversMax is a calculated column is the ENG table DATEDIFF(Eng[EngStartDate],TODAY(),MONTH)
The complexe part where I need help is that if a client change subscription, I want to keep counting him as active for both the original subscription and the new one.
My Client 1 for exemple switch Eng(subscription) in january. It's not an attrition.
So the EngUID1 should keep counting until he really end.
In order for this to work I concider a subscription to end after 2 concecutives months without payment.
If he start again later it should be brand new and not concidered. As for the TRX 9 in my exemple.
Could someone help me plz?
Thanks in advance 🙂
