cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heastham
Helper I
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 IDSession DateHeight (cm)Bio Age
00101/04/2019130.78.8
00118/06/2020133.79.3
00109/03/2021134.99.7


Table 2 - Sprint: 

Player IDSession DateSprint Time - 5mBio Age
00101/05/20193.21?
00110/05/20203.18?
00124/05/20213.17?

 

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

1 ACCEPTED SOLUTION
Fowmy
Super User
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 = 
    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       
        
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
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 = 
    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       
        
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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?

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

Thank you so much for your input 😀

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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