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
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