calculated columns: Lost_date and reactivated_date

[Anonymous]

Hi, I wish to get help on a problem that I am trying to solve. I have a list of patients and their visit dates to a clinic. I want to create a column two columns: 1) Lost_date 2) reactivated date Lost Date: A patient becomes lost/inactive if he has not visited the clinic for more than 18 months since his/her last visit Reactivated-date: if a patient comes back to the clinic after being inactive for more than 18 months, then the new visit date is considered as reactivated_date Eg: Pat_Id: Visit_Date 2 5/6/2016 2 7/6/2016 ----- the patient becomes inactive on 1/6/2018 (the patient has not visited for 18 months) 2 5/8/2018 ----- the patient become reactivated on 5/8/2018 Any help would be great

[Anonymous]

Hi @Anonymous,

You can try to use following formulas, I use current user and date to find out previous/next date, then use diff between current and previous/next date as condition to mark them as lost or reactivated date.

Lost Date = 
VAR prevDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            [User] = EARLIER ( 'Table'[User] )
                && [Date] < EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    IF ( DATEDIFF ( prevDate, [Date] , MONTH ) >= 18, prevDate )

Reactivated date = 
VAR nextDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            [User] = EARLIER ( [User] )
                && [Date] > EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    IF (
        [Lost Date] <> BLANK (),
        IF ( DATEDIFF ( [Lost Date], nextDate, MONTH ) >= 18, nextDate )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin Sheng, Thank you for the dax. It partially solves the problem. The dax misses two important criteria in lost_date. 1) The lost date is directly taken as the previous date. But instead it should be 18 months from the previous date (Previous date + 18 months). Eg) Pat_id date Lost_date 1421 5/6/2016 1421 12/7/2018 11/6/2017 2) The lost date dax is not working on the patient's last visit date. It is only working for dates before the last date of visit for every patient. if the last visit of a patient is more than 18 months to the current date then the patient has to be considered as lost patient Eg) Pat_id date Lost_date 1421 5/6/2016 1421 12/7/2018 11/6/2017 1504 4/6/2016 10/6/2017 2157 10/7/2014 2157 1/7/2016 7/7/2018 Regards, Ruban

[Anonymous]

Hi Xiaoxin Sheng, Link to sample data: http://www.filedropper.com/lostpatientandreactivatedpatientsampledata. Hope this helps you.

[Anonymous]

Hi @Anonymous,

Did you mean you want these calculate columns are dynamic based on those two columns?

For example, if one of formula find out the lost date, another one should use previous lost data to find out the correspondent reactivated date, then use reactivated date to calculate next lost date and so on?

If this is a case, I don't think it is possible to achieve your requirement. Power bi dax formulas not support to do recursive calculation. Maybe you can try to calculate them in excel side, it contains row index to do recursive calculation.

Regards,

Xiaoxin Sheng

[Anonymous]

HI Xiaoxin Sheng, The formula that you shared with me just takes the previous date as the lost date. Well it should not be the previous date. It should actually be 18 months from the previous date. Hope I have clarified your question. Also the present formula doesn't work on the last visit date of a patient. A patient whose last visit was on (example) 5/6/2016, then the patient should also be considered lost as it has been more 18 month since the patient's last visit. Hope you could help me with a DAX that will solve both the conditions of lost patients Regards, Ruban Leslie J

[Anonymous · Accepted solution]

Hi @Anonymous,

I modify calculated column formulas based on your expected result and sample data, please try to use it if it works:

Lost Date =
VAR prevDate =
    CALCULATE (
        MAX ( Lost[Visit_Date] ),
        FILTER (
            ALL ( Lost ),
            [patid] = EARLIER ( [patid] )
                && [Visit_Date] < EARLIER ( Lost[Visit_Date] )
        )
    )
RETURN
    IF (
        DATEDIFF ( prevDate, [Visit_Date], MONTH ) >= 18,
        DATE ( YEAR ( prevDate ), MONTH ( prevDate ) + 18, DAY ( prevDate ) )
    )


Reactive Date =
VAR nextDate =
    CALCULATE (
        MIN ( Lost[Visit_Date] ),
        FILTER (
            ALL ( Lost ),
            [patid] = EARLIER ( Lost[patid] )
                && [Visit_Date] >= EARLIER ( [Lost Date] )
        )
    )
RETURN
    IF ( [Lost Date] <> BLANK () && nextDate <> BLANK (), nextDate )

Regards,
Xiaoxin Sheng

[kevinWH]

Hi Sheng,

Could you help me with creation of a calculated column or custom column with the below as the logic

if( pat_id = earlier (pat_id),

  if( datediff (Procdate, earlier (Procdate), month ) >18,

     date( year( earlier(Procdate) ) , month (earlier(Procdate) )+ 18 , day( earlier(Procdate) ) ) ) ,

  if( datediff ( Procdate, today(), day) > 548,

      date( year( Procdate ) , month (Procdate )+ 18 , day( Procdate ) ) )

  )

 --- pat_id and Procdate are column of table

Regards,

KevinWH

[Anonymous]

Hi @Anonymous,

Hmm, so you mean if next visit date is large than 18 months, it should mark the correspond lost date at current row and mark next visit date as reactivated date?

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin Sheng,

I understand it is bit complicated. I have attached a file. Please download it. It is a .csv which has sample date and explanation for lost and reactivated date.

http://www.filedropper.com/lostpatientandreactivatedpatientsampledata_1

Regards,

Ruban Leslie J

[AlB]

Hi @Anonymous

Could you post sample data (or the pbix)?

[Anonymous]

Hi, Sorry for my ignorance. How to share a sample data in power bi community questions? I have don't it long time back but don't know how to do it now. Is there a place to attach a file ?

[AlB]

@Anonymous

You have to post here the URL to the file, either from platforms like OneDrive, Dropbox... or upload the file to sites like http://www.tinyupload.com/ or https://www.filedropper.com/ (no sign-in required) and share the URL

[Anonymous]

Hi AIB, Link to sample data: http://www.filedropper.com/lostpatientandreactivatedpatientsampledata