DAX - Get the next closest value based on Date filter

[Anonymous]

Hello,

I have this table:

And, based on a Calendar Date filter, I want to get the Value based on the closest date. 

For example

- If I filter the 7/12/2022, I want to get the Value = 23 (The next closest date in the table is 17/12/2022)

- If I filter the 22/12/2022, I want to get the Value = 21  (The next closest date in the table is 25/12/2022)

- If I filter the 20/12/2022, I want to get the Value = 15

I need to perform this on DAX. Thanks

[AlB · Accepted solution]

Hi @Anonymous 

Try this measure. It assumes there is NO relationship between your date and fact tables. FactT is the table you show above. 

Measure_ =
VAR selected_ = SELECTEDVALUE ( CalendarT[Date] )
VAR lower_ =
    CALCULATE ( MAX ( FactT[Date] ), FactT[Date] <= selected_ )
VAR higher_ =
    CALCULATE ( MIN ( FactT[Date] ), FactT[Date] > selected_ )
RETURN
    IF (
        ABS ( higher_ - selected_ ) < ABS ( lower_ - selected_ ),
        CALCULATE ( DISTINCT ( FactT[Value] ), FactT[Date] = higher_ ),
        CALCULATE ( DISTINCT ( FactT[Value] ), FactT[Date] = lower_ )
    )

If this does not work, do share a pbix with sample data

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.