Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
keivan
Helper I
Helper I

Calculating renewal success

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

 

My business case:
I provide internet service licenses. When a customer of mine subscirbe a licence pack he create a connection (es. connection_id 2001). During the subscription I ask the client to provide a consent for treating its personal data (es. consent_id 8009).
The consent has an expiration date (for example 25/08/2022).

I would like to know how many customers after the first subscription, when the consent expires provide another the consent for the data treatment.

Please note that in my dataset when
 the client renew the consent, a new consent_id will be generate (consent_id 8055) but the connection_id would NOT change (connection_id 2001).

Here more info:

 

consent_business_idconnection_business_idfetch_business_idconsent_statusconsent_creation_dateconsent_valid_untilconsent_okconsent_newconsent_fail
800920017771ok2022-03-092022-06-07100
805520017772ok2022-06-192022-09-17100
800220017773ok2022-09-182022-12-17100
808120027774new2022-09-19 010
805720037775ok2022-03-212022-06-19100
805320047776fail2022-09-19 001
801620047777ok2022-04-022022-07-01100
801720047778new2022-07-01 010

 

as you may see for connection_business_id = 2001 a first consent (consent_id: 8009) was given on 2022-03-09. This consent expired after 90 days and it was renewed on 2022-06-19 (consent_id: 8055). 
Then after even consent_id: 8055 expired a third consent (consent_id: 8002) was granted on 9/18/2022.
As you may see in the table all threes has the same connection_id

 

Otherwise, connection_id = 2003 after expiration no attempt of reneal was made.
While connection_id = 2004 the first consent expired on  7/1/2022 an attempt of renewal begun the same 7/1/2022 but it fwas never completed so consent_status = new.

 

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 consent_valid_until < TODAY() -14

Then in this specific case

consent_renewal % = 2/3 = 66.7%.

Out of the 4 connections only 3 (2001, 2003, 2004) have a consent with status = ok and expired by more than 14 days (consent_id: 8055, 8057, 8016). Connection: 2001, reconnect successfully and create consent 8002, connection: 2003 never reconnect, conenction: 2004 attempt unsuccessfully the reconnect. consent_renewal % = 2/3 = 66.7%. (I count here connection 2001, 2004)

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

Thanks,

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @keivan 

 

You can try the following methods.
Measure:

 

Expires 14 days =
VAR _Maxdate =
    CALCULATE ( MAX ( 'Table'[consent_valid_until] ),
        FILTER ( ALL ( 'Table' ),
            [consent_status] = "ok"
                && [connection_business_id] = SELECTEDVALUE ( 'Table'[connection_business_id] )
        )
    )
RETURN
    IF ( _Maxdate <> BLANK () && TODAY () - _Maxdate > 14, 1, 0 )
consent_renewal% = 
Var _N1=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[Expires 14 days]=1))
Var _N2=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[consent_status]="ok"))
Return
DIVIDE(_N1,_N2)
consent_renewal_success% = 
Var _fail=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[consent_valid_until]=BLANK()&&[consent_status]="fail"))
Var _success=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[Expires 14 days]=1))
Return
DIVIDE(_success-_fail,_success)

 

vzhangti_0-1670921585511.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

View solution in original post

1 REPLY 1
v-zhangti
Community Support
Community Support

Hi, @keivan 

 

You can try the following methods.
Measure:

 

Expires 14 days =
VAR _Maxdate =
    CALCULATE ( MAX ( 'Table'[consent_valid_until] ),
        FILTER ( ALL ( 'Table' ),
            [consent_status] = "ok"
                && [connection_business_id] = SELECTEDVALUE ( 'Table'[connection_business_id] )
        )
    )
RETURN
    IF ( _Maxdate <> BLANK () && TODAY () - _Maxdate > 14, 1, 0 )
consent_renewal% = 
Var _N1=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[Expires 14 days]=1))
Var _N2=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[consent_status]="ok"))
Return
DIVIDE(_N1,_N2)
consent_renewal_success% = 
Var _fail=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[consent_valid_until]=BLANK()&&[consent_status]="fail"))
Var _success=CALCULATE(DISTINCTCOUNT('Table'[connection_business_id]),FILTER(ALL('Table'),[Expires 14 days]=1))
Return
DIVIDE(_success-_fail,_success)

 

vzhangti_0-1670921585511.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors