Reconnection rate -

[keivan]

Hi all,

I would like to calculate reconnection rate.
My dataset is compose in this way:

Calendar Table (Date, Year, Month, etc.)
Connections Table (connection_id, etc.)
Consents Table (consent_id, connection_id, valid_until, etc.)

When a client create a connection (connection_id 1111) he provide a consent (consent_id 2222) this consent has an expiration date (for example 25/08/2022). When this consent expire the client can renew the consent for its connection. 
If the client renew the consent, a new consent_id will be generate (consent_id 3333) but the connection_id would NOT change (connection_id 1111).

I had already calculate a formula to find out the

Last Consent Expiration Date = 

CALCULATE (
MAX (consents[consent_valid_until] ),
FILTER (
Consents,
consents[connection_business_id] = MAX ( Connections[connection_business_id] )
)
)

However when I try to transform it in a boolean I got always 0 values 

Active Connection = 

VAR _active =

CALCULATE (
MAX (consents[consent_valid_until] ),
FILTER (Consents,
consents[connection_business_id] = MAX ( Connections[connection_business_id] )
)
)
RETURN
IF ( _LASTDATE >= TODAY (), 1, 0 )

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, I want to check if a new consent has been created. So if a renewal was perform.

I don't know how to do that 😞 

[Greg_Deckler]

@keivan Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[keivan]

Thanks Greg, 
I checked but I did not find this specific topin on most popular questions.
I would always add table in future.

here more info:
date are in format yyyy-mm-dd hh:mm:ss.000

consent_business_id	connection_business_id	consent_status	consent_creation_date	consent_creation_date_1	consent_valid_until	consent_ok	consent_new	consent_fail
11	2	ok	2022-09-05 12:00:00.000	2022-09-05	2022-12-04 12:00:00.000	1	0	0
15	2	ok	2022-12-06 05:54:20.000	2022-12-06	2023-03-06 05:54:20.000	1	0	0
12	69	ok	2022-08-06 08:42:15.000	2022-12-06	2022-11-04 08:42:15.000	1	0	0
16	69	new	2022-08-09 08:42:15.000			0	1	0
17	77	fail	2022-12-06 05:53:16.000			0	0	1

as you may see for connection_business_id = 2 a connection was created on 5th Sept. it expired after 90 days (consent_id = 11). Then it was renew (consent_id = 15).

connection_id = 69 after the first consent = 12 expired, an attempt of renewal begun but it fail. consent_status = fail.

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 (to give people time to renew).

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

Then in this specific case
consent_renewal % = 2/2 = 100%. Out of the 5 consents only 3 are ok and only 2 are expired by more than 14 days (consent_id 11 and 12)

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

Pleas elet me know if it is clear or still confusing the question.
Thanks,

[keivan]

Hi @Greg_Deckler 

did you had chance to take a look?