Forum Discussion

keivan's avatar
keivan
Helper I
3 years ago

Reconnection rate -

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 😞 

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
      keivan
      Helper I
      3 years ago

      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_idconnection_business_idconsent_statusconsent_creation_dateconsent_creation_date_1consent_valid_untilconsent_okconsent_newconsent_fail
      112ok2022-09-05 12:00:00.0002022-09-052022-12-04 12:00:00.000100
      152ok2022-12-06 05:54:20.0002022-12-062023-03-06 05:54:20.000100
      1269ok2022-08-06 08:42:15.0002022-12-062022-11-04 08:42:15.000100
      1669new2022-08-09 08:42:15.000  010
      1777fail2022-12-06 05:53:16.000  001

       

       

      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,