cancel
Showing results for
Did you mean:
Helper I

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

@heastham

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

@heastham

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?

Helper I

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

Thank you so much for your input 😀

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors