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

[Anonymous]

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. 

[Fowmy · Accepted solution]

@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       
        

[fingalbrad]

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?

[Anonymous]

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

Thank you so much for your input 😀