Find the Nearest Future Date to User Selected Date

[julesdude]

Hi all,
I have a table looking a little like this:

Reference	Date
AAA	12/05/2021
AAA	20/12/2022
AAA	01/02/2023
BBB	01/12/2022
BBB	04/02/2023

I need help with some DAX that essentially filters my table by the reference and returns the Date that is the nearest date in the future to today's date (or the [as of date] measure I have in my measures that itself finds the MAX value of a drop down date filter the user can use to select a date) 

I'm able to find solutions for this that involve creating a DAX column against a table in my model. However, this won't work for my case because the column would then not update when the user chooses a different date, as far as I understand it.

[v-jialluo-msft]

Hi  @julesdude ,

Please follow these steps:
(1) Create a new measure

FLAG = 
VAR _SELECT = IF(ISFILTERED('CALENDAR'[Date]),MAX('CALENDAR'[Date]),BLANK())
VAR _TABLE = CALCULATE(VALUES('Table'[Date]),TOPN(1,FILTER(ALL('Table'),'Table'[Date] >= _SELECT),'Table'[Date],ASC))
RETURN IF(MAX('Table'[Date] )= _TABLE ,1, 0)

(2)Apply filtering

(3)Final output

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[julesdude]

Thank you too @v-jialluo-msft 
It is a solution for someone else perhaps - just for me I needed a way without using a filter formula or DAX column scenario.

[FreemanZ]

hi @julesdude 

For the nearest future date per referencee, you may created column with the code below:

Column =

CALCULATE(

    MIN(TableName[Date]),

    ALLEXCEPT(TableName, TableName[Reference]),

    TableName[Date]>=TODAY()

)

I tried and it worked like this:

for the (or the [as of date]....) part, not sure about your expectation. 

[julesdude]

Hi @FreemanZ 

Many thanks for your reply.

Your line:
 TableName[Date]>=TODAY()

I am not sure your solution would work because TODAY() should not be used. The date used should be that selected by the user in the date picker I have provided in the report:

I assigned the selection made here to the measure [As Of Date]:

As Of Date = CALCULATE(MAX(datetable[Date]), ALLSELECTED(DateTable))

I think if a DAX column is created for this solution then that column won't update dynamically when the date is changed byt the user....unless I am missing something??

[FreemanZ · Accepted solution]

Hi @julesdude 

something like this?

the code:

EarliestFutureDate =

VAR DateSelection =MIN('Calendar'[Date])

RETURN

CALCULATE(

    MIN(TableName[Date]),

    TableName[Date]>=DateSelection

)

[julesdude]

@FreemanZ thank you so much. A simple solution actually than I had thought!

The only change I made was to change the MIN('Calendar'[Date]) to MAX('Calendar'[Date]) to gain the user's selection from the date picker - I hide the first drop-down of this slicer from view so the user cannot change the minimum date in it.

Many thanks again!