Status of most recent task

[aflintdepm]

I have a table of appointments that fall into 2 categories- Confirmed or Unconfirmed.  For each Unconfirmed, there is a reason.  I need to return the reason based on only the most recent unconfirmed appointment.  I can calculate when the most recent unconfirmed appointment was, but I can't figure out how to return the reason.

Data

Appointment Date	Status	Reason
5/1/24	Confirmed
6/1/24	Unconfirmed	Cancelled
7/1/24	Unconfirmed	No Show

My formula for most recent unconfirmed date would be:
Most Recent = CALCULATE(MAX('Table'[Appointment Date]), 'Table'[Status]="Unconfirmed", 'Table'[Appointment Date]<=TODAY()

Using the data sample above, I would get 7/1/24 as my result.  Now, how do I get "No Show"?

Thank you in advance for any help

[FreemanZ]

or try to plot a table visual with date column, status column and a measure like:

Measure = 
VAR _currentdate = MAX(data[date])
VAR _statuspre =
    MAXX(
        TOPN(
            1,
            FILTER(ALL(data), data[date]<_currentdate),
            data[date]
        ),
        data[status]
    )
VAR _result = 
    SWITCH(
        _statuspre,
        "Confirmed", "Cancelled",
        "Unconfirmed", "No Show", 
        ""
    )
RETURN _result

it worked like:

[FreemanZ]

Hi @aflintdepm ，

not sure if i fully get you, try to add a calculated column like:

column = 
VAR _currentdate = [date]
VAR _statuspre =
    MAXX(
        TOPN(
            1,
            FILTER(data, data[date]<_currentdate),
            data[date]
        ),
        data[status]
    )
VAR _result = 
    SWITCH(
        _statuspre,
        "Confirmed", "Cancelled",
        "Unconfirmed", "No Show", 
        BLANK()
    )
RETURN _result

it works like:

[Irwan · Accepted solution]

hello @aflintdepm 

your DAX looks good. If you want to get 'Reason' value, just use your DAX but change value inside MAX from date into reason.

Most Recent Reason = CALCULATE(MAX('Table'[Reason]), 'Table'[Status]="Unconfirmed", 'Table'[Appointment Date]<=TODAY())

Hope this will help you.

Thank you.