Calculation of renewal rate

keivan — Mon, 12 Dec 2022 16:37:09 GMT

Hi all,

I would like to calculate consent renewal rate.

On my dataset when a connection is created (connection_id 2001) we collect also a consent from the client (consent_id 8009). Consent has an expiration date. When the consent expired (for example 9/17/2022) the client can renew the consent for its connection.

If the client renew the consent, a new consent_id will be generate but the connection_id would NOT change. In the table below for connection_id 2001 when consent_id 8009 expired a new consent_id: 8055 is provided

What I actually want to do is to calculate Renewal rate.

So for those connection in which the consent is expired by at least 14 days (to give people some time to renew), I want to check if a new consent has been created after old one is expired.

Here how i would perform calculation

consent_business_id	connection_business_id	fetch_business_id	consent_status	consent_creation_date	consent_valid_until	consent_ok	consent_new	consent_fail
8009	2001	7771	ok	2022-03-09	2022-06-07	1	0	0
8055	2001	7772	ok	2022-06-19	2022-09-17	1	0	0
8002	2001	7773	ok	2022-09-18	2022-12-17	1	0	0
8081	2002	7774	new	2022-09-19		0	1	0
8057	2003	7775	ok	2022-03-21	2022-06-19	1	0	0
8053	2004	7776	fail	2022-09-19		0	0	1
8016	2004	7777	ok	2022-04-02	2022-07-01	1	0	0
8017	2004	7778	new	2022-07-01		0	1	0

as you may see for connection_business_id = 2001 a consent was created on 2022-03-09. Consent expired after 90 days (consent_id = 8009). Then it was renew (consent_id = 8055) on 2022-06-19.
Then again expired 2022-09-17 and was another time renewed (8002).
For this mock sample I provide random number, but actually all id in my dataset are progressive numbers

What I want to get is a KPI, reporting the % of how many conenctions are renew. So I want to take into consideration only consent ok and expired by at least 14 days

where consent_status = 'ok' and consen_valid_until < TODAY() -14

Then in this specific case

consent_renewal % = 2/3 = 67%. Out of the 4 connection only 3 (connection_id 2001, 2003, 2004 ) have a consent in status ok and expired by more than 14 days (consent_id 8055, 8057, 8016)

consent_renewal_success % = 1/2 = 50% Out of 2 renewal only 1 is with consent_status_ok (consent_id = 8002).

Re: Calculation of renewal rate

keivan — Mon, 12 Dec 2022 16:40:56 GMT

You can fin a mock dataset here https://drive.google.com/file/d/1sMMqlTmQ76mCe9-PHdk-hLjPzM4kvE_l/view?usp=share_link

Re: Calculation of renewal rate

v-jianboli-msft — Tue, 13 Dec 2022 06:28:00 GMT

Hi keivan ,

You said:

"Out of the 4 connection only 3 (connection_id 2001, 2003, 2004 ) have a consent in status ok and expired by more than 14 days (consent_id 8055, 8057, 8016)"

so shouldn't consent_renewal % be 3/4? If it is 2/3, then what is the logic of determining 2?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: Calculation of renewal rate in DAX Commands and Tips

Calculation of renewal rate

Re: Calculation of renewal rate

Re: Calculation of renewal rate