Forum Discussion

akshaydz's avatar
akshaydz
Frequent Visitor
8 years ago
Solved

start_date end_date from a table

Hi,

 

I have scenerai where I need to pull the patients first_join date and patients_last join date, My data looks somethings like this

 

PatientTreatmentstart_dateEnd_dateStatus
P1T119/05/201622/12/20160
P1T202/02/201704/08/20170
P1T310/10/201701/01/20180
P2T422/02/201722/06/20170
P2T513/05/201722/12/20170
P2T622/08/2017Null1
P3T724/12/201723/06/20180
P4T826/12/2017Null1

For each Patient:

    --Start_date = First(start_date) of corresponding patients start_date
    --End_date = If Status is 1 then Today else Last(end_date) of corresponding patients end_date

 

The output would look like this

 

Patient_idStart_dateEnd_date
P119/05/201601/01/2018
P222/02/2017Today()
P324/12/201723/06/2018
P426/12/2017Today()

Can anybody help me with this please?

Need Help
  • Thejeswar's avatar
    Thejeswar
    8 years ago

    Hi akshaydz,

    For this purpose, I converted your date column into mm/dd/yyyy format from your format of dd/mm/yyyy. Hopw that would not aaffect you in any way

     

    Then create 2 columns using the following 2 DAX Statements

     

    First Date = CALCULATE(MIN(Table1[start_date]), FILTER(Table1, Table1[Patient] = EARLIER(Table1[Patient]))) 
    Last Date = IF(ISBLANK(CALCULATE(MAX(Table1[End_date]), FILTER(Table1, Table1[Patient] = EARLIER(Table1[Patient])))), TODAY(), CALCULATE(MAX(Table1[End_date]), FILTER(Table1, Table1[Patient] = EARLIER(Table1[Patient]))))

    The Final Table Looks as the one below

     

     

     

     

1 Reply

  • Thejeswar's avatar
    Thejeswar
    Super User
    8 years ago

    Hi akshaydz,

    For this purpose, I converted your date column into mm/dd/yyyy format from your format of dd/mm/yyyy. Hopw that would not aaffect you in any way

     

    Then create 2 columns using the following 2 DAX Statements

     

    First Date = CALCULATE(MIN(Table1[start_date]), FILTER(Table1, Table1[Patient] = EARLIER(Table1[Patient]))) 
    Last Date = IF(ISBLANK(CALCULATE(MAX(Table1[End_date]), FILTER(Table1, Table1[Patient] = EARLIER(Table1[Patient])))), TODAY(), CALCULATE(MAX(Table1[End_date]), FILTER(Table1, Table1[Patient] = EARLIER(Table1[Patient]))))

    The Final Table Looks as the one below