Retention rate

[MyOx]

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

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 🙂

[Anonymous]

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 Community

Best Regards,
Gao
Community Support Team

If 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

[MyOx]

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 be

CliNo 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.