find nearest date

[joslaz]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]01.01.2019[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]28.01.2019[/TD]
[TD]101[/TD]
[/TR]
[TR]
[TD]27.02.2019[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]30.03.2019[/TD]
[TD]103[/TD]
[/TR]
</tbody>[/TABLE]


TblCAl
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Month[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]01.01.2019[/TD]
[TD]Jan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02.01.2019[/TD]
[TD]Jan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03.01.2019[/TD]
[TD]Jan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04.01.2019[/TD]
[TD]Jan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01.02.2019[/TD]
[TD]Feb[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01.03.2019[/TD]
[TD]Mar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01.04.2019[/TD]
[TD]Mai[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

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]

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]

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]

Perfect! It works!
Thanks