How to use Set Analysis To Obtain the Next Car Repair Date Without Getting it's Maximum Date

[griffin2571]

My Expression was set to Max[Next Car Repair], however, I am trying to obtain the next car repair date. 

For example in the Next Car Repair Column I am trying to obtain 09/25/2022 rather than 09/17/2023. What is the best way to use set expression for this?

Category	Last Car Repair	Next Car Repair
Car Repair	5/15/2009	4/6/2012
Car Repair	9/9/2009	-
Car Repair	10/23/2014	11/30/2019
Car Repair	9/28/2018	9/25/2022
Car Repair	4/17/2020	9/17/2023

[Nicole-Smith]

Do you mean to get the next one after today?  If so, this should work:
DATE(MIN({<[Next Car Repair] = {">=$(=TODAY())"}>} [Next Car Repair]))

However, for the example data in your post, it is still going to return 9/17/2023 because 9/25/2022 is before today.

[griffin2571]

Hello,

No I am trying to obtain 9/25/2022. But i see the logic that is being used here. What if instead of 09/25/2022 the date was 11/15/2022 as a replacement but it is not the next date?

Category	Last Car Repair	Next Car Repair
Car Repair	5/15/2009	4/6/2012
Car Repair	9/9/2009	-
Car Repair	10/23/2014	11/30/2019
Car Repair	9/28/2018	11/15/2022
Car Repair	4/17/2020	9/17/2023

[griffin2571]

Actually I have a follow up to the original question... What if I am trying to get 09/25/2022 even if it's technically before today?

[Nicole-Smith]

If the "next date" isn't the first date after today, how do you determine what the "next date" is?  I need to know what logic you're trying to replicate before I can help you with the code.

[griffin2571]

Understand. The logic I am trying to capture is to get 09/25/2022 from the original data set. I am having trouble capturing the 9/25/2022 since it is the most recent date but it is before today and it is not the MAX date either. 

Category	Last Car Repair	Next Car Repair
Car Repair	5/15/2009	4/6/2012
Car Repair	9/9/2009	-
Car Repair	10/23/2014	11/30/2019
Car Repair	9/28/2018	9/25/2022
Car Repair	4/17/2020	9/17/2023

[Nicole-Smith · Accepted solution]

This works for the limited amount of sample data you provided:
DATE(MIN({<[Next Car Repair] = {"=RANK(-FABS([Next Car Repair] - TODAY())) = 1"}>} [Next Car Repair]))

It will give you the date closest to today whether it's in the past or future.