Measure to format next datetime from NOW() in list of datetimes in a table visual

[SBAV01]

Hello

I am making a report which shows fulfillment of events to various departments. The main visual is a table which lists the events in DateTime order with a DateTime formatted field. There are filters and slicers for reletive date and department. The report is using DirectQuery. The report will function as a wallboard autorefreshing through the day and I want to indicate clearly which is the next event in the filtered list so at a glance they can see if they are ahead or behind on their fulfillment. 

What I have in mind is including a mesaure which only returns a result for the next event visible in the list and use that measure with conditional formatting on the row which is the next event.

Currently my attempt at a measure looks like this:

NextEvent =

VAR CurrentDateTime = NOW()  

VAR NextEventDateTime = MIN('Transport'[DateTime])  

RETURN

    IF(

        CurrentDateTime < NextEventDateTime,  

        "Next Event", 

        BLANK()  

    )

But I think my issue is that it is evaluating the Variable against each line of the table so the result is that all future events get the "Next Event" tag.
Trying to do some reading and I think I need to use some combination of Calculate / AllSelected / Filter within the variable.

Sample table

EventID	Day	DateTime	Department	Outstanding	Fulfilment%
101	Tue	21/03/2023 00:00	X	0	100.00%
139	Tue	21/03/2023 07:30	X	189	90.17%
145	Tue	21/03/2023 07:30	X	0	100.00%
415	Tue	21/03/2023 08:00	X	0	100.00%
394	Tue	21/03/2023 09:01	X	40	95.88%
378	Tue	21/03/2023 17:00	X	170	48.33%
400	Wed	22/03/2023 09:00	X	0	100.00%
284	Wed	22/03/2023 09:01	X	748	60.65%
163	Wed	22/03/2023 09:01	X	328	9.89%
459	Wed	22/03/2023 09:01	X	762	34.20%

Anyone help point me in the right direction?
Many Thanks

[lbendlin]

Define "next".  For example which event is "next"  after event 400 ?

[SBAV01]

After 400 would be 284 & 163 & 459. I don't mind how the measure would treat rows with the same DateTime - The simpler the better would help me understand but if I had a preference it would only return for the the first matching number. In this case 284.
Thank you

[lbendlin]

then slap an index column onto your raw data (assuming it is sorted by date) and grab the row that has the next index.  Or use the OFFSET function.  

Do you want this in DAX or in Power Query?  calculated column or measure?

[SBAV01]

A DAX Measure would be most suitable I think as it is a direct query and the pages are filtered for different departments so it needs to figure out the 'next' event per filtered table.
Is the Index column the solution for where the 'next' event has matching DateTimes with others?
Thanks

[lbendlin]

yes, it's to break the ties.  Alternatives would be to use TOPN(1,xxx) but these may be more costly.  Doesn't matter if you want it as a measure - there the index won't help too much.

[SBAV01]

Thanks - How do I actually go about grabbing the next index or datetime when displaying a range of events in a table?

Kind Regards
Stephen

[lbendlin]

Grab the "current" index, then find the smallest index that is bigger than that, then the date for that index.