Forum Discussion

julesdude's avatar
julesdude
Icon for Post Partisan rankPost Partisan
3 years ago
Solved

Find the Nearest Future Date to User Selected Date

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

ReferenceDate
AAA12/05/2021
AAA20/12/2022
AAA01/02/2023
BBB01/12/2022
BBB04/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.

Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    Hi julesdude 

     

    something like this?

     

    the code:

    EarliestFutureDate =
    VAR DateSelection =MIN('Calendar'[Date])
    RETURN
    CALCULATE(
        MIN(TableName[Date]),
        TableName[Date]>=DateSelection
    )

6 Replies

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      julesdude
      Icon for Post Partisan rankPost Partisan
      3 years ago

      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's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      julesdude
      Icon for Post Partisan rankPost Partisan
      3 years ago

      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's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        Hi julesdude 

         

        something like this?

         

        the code:

        EarliestFutureDate =
        VAR DateSelection =MIN('Calendar'[Date])
        RETURN
        CALCULATE(
            MIN(TableName[Date]),
            TableName[Date]>=DateSelection
        )