cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Super User

@Anonymous

Add the following column to the Growth Table:

Closest Bio Age=
var __player = Sprint[Player ID]
var __sprdate = Sprint[Session Date]
var __growth =
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

Did I answer your question? Mark my post as a solution! and hit thumbs up
3 REPLIES 3
Super User

@Anonymous

Add the following column to the Growth Table:

Closest Bio Age=
var __player = Sprint[Player ID]
var __sprdate = Sprint[Session Date]
var __growth =
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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper I

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

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

Thank you so much for your input 😀