cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sfooshee
Regular Visitor

Return the last date with criteria

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 IDVisit DateCompletedPatient StatusFirst Visit Date
11/1/21YesNew 
21/2/21YesNew 
31/3/21NoNew 
31/4/21YesNew 
18/31/21YesActive 
19/1/21YesActive 
29/2/21YesActive 
39/3/21YesActive 

 

Screen Shot 2021-08-31 at 3.52.44 PM.png

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

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

View solution in original post

@CNENFRNL That's perfect. Thanks so much!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.