Next Occurrence of value

[Mattula]

I have a table of events and I want in a card to retrieve the next event which has the status 'Planned'. 

My table looks like this 

Date	Status	Event
11/12/2023	Confirmed	Event 1
12/12/2023	Confirmed	Event 2
13/12/2023	Cancelled	Event 3
14/12/2023	Confirmed	Event 4
15/12/2023	Planned	Event 5
16/12/2023	Cancelled	Event 6
17/12/2023	Planned	Event 7
18/12/2023	NEW	Event 8
19/12/2023	NEW	Event 9
20/12/2023	Planned	Event 10

I have used this Measure which is retrieving a value but isn't the next occuring event in the date order from today's date 

Next Event =

CALCULATE(MIN(TABLE1[EVENT]),

FILTER(

    TABLE1,

    TABLE1[EVENT_STATUS] = "Planning" &&

    TABLE1[Date] >= NOW()

)

)

[vicky_ · Accepted solution]

Here's the measure I have: 

Next Event = 
var earliestPlannedDate = CALCULATE(MIN('Table'[Date]), 'Table'[Date] >= TODAY() && 'Table'[Status] = "Planned")
return CALCULATE(MIN('Table'[Event]), 
    FILTER(
        'Table',
        'Table'[Date] = earliestPlannedDate
    )
)

Just a quick note - the TODAY() and NOW() functions are based on UTC time, but you should be able to find many threads on here about converting to local time. 

And the reason that your measure didn't work is because the MAX([Event]) will grab the first event alphabetically, so between events 7 and event 10, the "1" would come before "7", resulting in the incorrect result you saw above.

[Mattula]

This worked perfectly. Thank you