Return the last date with criteria

[sfooshee]

I couldn't find anything that answered this so I thought I would ask it here. I'm trying to create a calculated column in BI that displays each patient's first visit date. I have tried LASTDATE and MAX and can't quite figure it out. 

Basically, the cell needs to display the most recent visit date from a row where that meets these criteria:

1. The patient ID matches the current row (I usually use the EARLIER function)
2. Completed = "Yes"
3. Patient Status = "New"

Attached below is a sample data table and a screenshot of the result.

Thank you so much!

Patient ID	Visit Date	Completed	Patient Status	First Visit Date
1	1/1/21	Yes	New
2	1/2/21	Yes	New
3	1/3/21	No	New
3	1/4/21	Yes	New
1	8/31/21	Yes	Active
1	9/1/21	Yes	Active
2	9/2/21	Yes	Active
3	9/3/21	Yes	Active

[CNENFRNL · Accepted solution]

First Visit Date =
MINX (
    FILTER (
        'Table',
        'Table'[Patient ID] = EARLIER ( 'Table'[Patient ID] )
            && 'Table'[Patient Status] = "New"
            && 'Table'[Completed] = "Yes"
    ),
    'Table'[Visit Date]
)

[sfooshee]

@CNENFRNL That's perfect. Thanks so much!