topic Re: LOOKUPVALUE - Return a value closest to a date in another table in DAX Commands and Tips

LOOKUPVALUE - Return a value closest to a date in another table

Anonymous — Mon, 24 May 2021 14:37:25 GMT

Hi There,

Aim: Create a Calculated Column that returns the value closest to the test date.

I have a 'Growth' table (Table 1) and a 'Sprint' table (Table 2). The growth table contains the a Bio Age value. I would like to append Bio Age into the Sprint table in order to figure out what a player's Bio Age was at the time they took a sprint test. A player could have undertake a sprint test at a different time to getting their measurements taken (which are stored in the Growth table). Therefore, I need to find the Bio Age of a player that has been recorded closest to the session date that the sprint test was undertaken.

Relationship: There is no relationship between the two tables as there are duplicate values within the two - which is why I thought LOOKUPVALUE may be the best method of finding the value. PlayerID is present in both tables.

Table 1 - Growth:

Player ID	Session Date	Height (cm)	Bio Age
001	01/04/2019	130.7	8.8
001	18/06/2020	133.7	9.3
001	09/03/2021	134.9	9.7

Table 2 - Sprint:

Player ID	Session Date	Sprint Time - 5m	Bio Age
001	01/05/2019	3.21	?
001	10/05/2020	3.18	?
001	24/05/2021	3.17	?

Any help would be greatly appreciate. Many thanks in advance.

Re: LOOKUPVALUE - Return a value closest to a date in another table

Fowmy — Mon, 24 May 2021 15:08:04 GMT

Anonymous

Add the following column to the Growth Table:

Closest Bio Age= var __player = Sprint[Player ID] var __sprdate = Sprint[Session Date] var __growth = ADDCOLUMNS( FILTER( Growth, Growth[Player ID] = __player ), "diff", ABS(DATEDIFF(__sprdate , Growth[Session Date] , DAY )) ) var __mindiff = MINX( __growth,[diff]) var __age = MAXX( FILTER( __growth, [diff] = __mindiff ), Growth[Bio Age] ) return __age

Re: LOOKUPVALUE - Return a value closest to a date in another table

Anonymous — Mon, 24 May 2021 15:45:43 GMT

So far so good! You my friend are a dream!

Thank you so much for your input 😀

Re: LOOKUPVALUE - Return a value closest to a date in another table

fingalbrad — Tue, 18 Oct 2022 09:39:54 GMT

How would you change the code if Player ID didnt exist?? and session date was just a numerical field and you had to look up and return the Bio Age based on the closest value to the numerical field?