Forum Discussion
Retention rate
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)
| CliUID |
| 1 |
2 |
| 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
Return
--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.
In order for this to work I concider a subscription to end after 2 concecutives months without payment.
Could someone help me plz?
Thanks in advance 🙂
2 Replies
Hi MyOx ,
Please consider providing a sample file without privacy. It would be very helpful, thanks.
How to provide sample data in the Power BI Forum - Microsoft Fabric CommunityBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
here's my data in xlsx:
https://oxfam.box.com/s/6bnv1gazuwlvlg9gjqg930o938l1bpba
And here's my Pbix:
https://oxfam.box.com/s/1eej9y1qizyvnzwi3phnghi33irqh8bt
There's already the matrix I can do in it. I work well, but it only tells me the transaction for a given EngUID, not the following.
Goods Client exemples would beCliNo 250045,
He start in 2020-05 then switch in 2021-01 then switch again in 2022-12 (even though he missed a month), then switch again in 2023-11 (even though it's already have an other trx in 2023-11).CliNo 250043
He start in 2020-05 and end in 2020-06. Then there's a break. He start again in 2021-01 then swich in 2022-08. The Eng (subcription) of 2020 shouldn't be linked to the others
Transaction without EngUID shouldn't be concidered.
