Next Visit Type by Visit Date

[aflintdepm]

I have a table of client visits and visit dates (all future).  There are multiple visit types that could be scheduled, but I would like to report what the next visit type is, not the when.

Here is a small sample:

Patient Name	Visit Type	Visit Date
John Smith	Regular	7/25/2024
Betty Jones	Annual	8/1/2024
Bob Johnson	Regular	7/10/2024
John Smith	Annual	8/25/2024
Betty Jones	Regular	9/1/2024
Bob Johnson	Annual	8/15/2024

For this example, I would like the results to look like this:

Patient Name	Next Scheduled Visit Type
John Smith	Regular
Betty Jones	Annual
Bob Johnson	Regular

I know how to calculate when the next visit is, but I don't know how to return the value in Visit Type based on that calculation.

Any and all help appreciated.  Thank you

[gmsamborn · Accepted solution]

Hi @aflintdepm 

Would a measure like this help?

Next Visit Type = 
VAR _NextDate =
    CALCULATE(
        MIN( 'Table'[Visit Date] ),
        'Table'[Visit Date] > TODAY()
    )
VAR _NextType =
    CALCULATE(
        MAX( 'Table'[Visit Type] ),
        'Table'[Visit Date] = _NextDate
    )
RETURN
	_NextType