start_date end_date from a table

[akshaydz]

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

Patient	Treatment	start_date	End_date	Status
P1	T1	19/05/2016	22/12/2016	0
P1	T2	02/02/2017	04/08/2017	0
P1	T3	10/10/2017	01/01/2018	0
P2	T4	22/02/2017	22/06/2017	0
P2	T5	13/05/2017	22/12/2017	0
P2	T6	22/08/2017	Null	1
P3	T7	24/12/2017	23/06/2018	0
P4	T8	26/12/2017	Null	1

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_id	Start_date	End_date
P1	19/05/2016	01/01/2018
P2	22/02/2017	Today()
P3	24/12/2017	23/06/2018
P4	26/12/2017	Today()

Can anybody help me with this please?

[Thejeswar · Accepted solution]

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