# find nearest date



## joslaz (Aug 16, 2019)

Hello everyone,

I am looking for a Lookup-function in PowerPivot/ Dax to get the nearest date for a specific date.

I have the following two tables:

TblVal

DateValue01.01.201910028.01.201910127.02.201910230.03.2019103

<tbody>

</tbody>

TblCAl

DateMonthValue01.01.2019Jan02.01.2019Jan03.01.2019Jan04.01.2019Jan01.02.2019Feb01.03.2019Mar01.04.2019Mai

<tbody>

</tbody>

The objective/ challange is to get for each date (tblCal) the respective value from the tblVal.
So the 01.01.2019 should get the value 100. 

So every date between 01.01.2019 and 28.01.2019 should get the value 100.

Does anyone know how to manage this?



Best Regards
Joshua


----------



## jorismoerings (Aug 16, 2019)

Hi,

try a calculated column on Tblcal with this:
=
VAR mydate =
    CALCULATE (MIN ( Tblval[Date] ); FILTER ( CALCULATETABLE ( VALUES ( Tblval ) ); Tblval[Date] >= Tblcal[Date]))
RETURN
    CALCULATE (MIN ( Tblval[Value] ); FILTER ( CALCULATETABLE ( VALUES ( Tblval ) ); Tblval[Date]= mydate ))


----------



## joslaz (Aug 19, 2019)

Hey jorismoerings!
Thanks for your approach!

Is there maybe a sintax issue with RETURN? I put the function into the column in powerpivot. Is that right?


----------



## jorismoerings (Aug 20, 2019)

Hi @joslaz

No, there's no issue but perhaps you've broken the formula into 2 calculated fields.
If so, that's not the case, you need to copy the entire string as 1 formula into the calculated field and not in 2 separate fields


----------



## joslaz (Aug 20, 2019)

Perfect! It works!
Thanks


----------

