Filter or formula for Soonest date?

[DisraeliGears01]

Hi all,

Wondering if there's a filter or formula solution (not sorting) to filter all results down to only the "Soonest one" ranked according to date field. 

I have a form in one table linked to a view in another table, ideally so people can only sign up for one linked choice (it's a monthly event registration). Currently I have the filter set for Date within the next month which mostly works, but a day or two before the event, both the current and next month's start displaying (because it's only 28 days away, etc.) Perhaps my logic brain isn't on today, but I can't think of good filter terms to only include the soonest event. The other thought I had was a formula which would assign numbers or filters to the nearest date (-1 for past, 1 for soonest, 2 for next, etc.) and then I could make that the operative filter, but I don't see an obvious example of that in the Formula Reference Base or guidance. Any advice? 

[Blake_D]

This may be the long way...

Create a formula field that shows days until event. 

DATETIME_DIFF({Event Date}, TODAY(), 'days') 

Create a Event Order table to link all events to. Link all events to one record calling it Event Order. In the Event Order table create a rollup field to get the MIN(values) of "Days Until Event"

Create a lookup field in the main table to look at that rollup field. In the main table create a formula field "Filter for Soonest" 

IF({Days Until Event}={Days Until Closest Event},'Soonest Event',' ')

 Then create a view that filters for "Filter for Soonest" is not empty.

An automation to automatically link to the event order table when an event is created can help as well.

Hope this helps!