Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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 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

@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       
        
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

@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       
        
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?

Anonymous
Not applicable

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

Thank you so much for your input 😀

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors