Forum Discussion

keivan's avatar
keivan
Icon for Helper I rankHelper 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 (cons...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
    Icon for Helper I rankHelper 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,

    • keivan's avatar
      keivan
      Icon for Helper I rankHelper I
      3 years ago

      Hi Greg_Deckler 

      did you had chance to take a look?