topic Re: Status of most recent task in DAX Commands and Tips

Status of most recent task

aflintdepm — Wed, 24 Jul 2024 00:11:37 GMT

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

Re: Status of most recent task

Irwan — Wed, 24 Jul 2024 01:22:48 GMT

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.

Re: Status of most recent task

FreemanZ — Wed, 24 Jul 2024 02:44:23 GMT

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:

Re: Status of most recent task

FreemanZ — Wed, 24 Jul 2024 03:02:09 GMT

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: