How to pull most recent data based on a a data value from another table.

[drivas771994]

Hi,

I'm trying to pull data from one table based on the relationship to a date from another table. Here's the example.

I have two tables called tests and scans. In the test table i have test results that I want to compare against a bodyweight in my scans table. The issue is that they don't usually occur on the same day. So I have gotten my table to pull the closest date to which an athlete's bodyweight occurs in relation to the test but can't get the actual bodyweight. Here's my formula:

Most Recent Body Mass =

var dateweight=selectedvalue(SPARTA_SCANS2[Date])

return

calculate(min(TESTS[Date]), TESTS[Date]>=dateweight)

Below is a demo table of what I'm trying to get. The column in red is what I'm trying to get but can't

Athlete Name	Test Date	Test Result	Bodyweight Scan Date	Bodyweight in KG
Athlete A	1/1/2022	60	12/28/2021	80
Athlete A	2/1/2022	70	2/3/2022	78
Athlete A	3/1/2022	80	3/2/2022	79

[v-yanjiang-msft · Accepted solution]

Hi @drivas771994 ,

According to your description, you have two tables, SPARTA_SCANS2 and TESTS, I create a sample.

Test table:

SPARTA_SCANS2 table:

You want to get the Body weight of the latest Test Date which are greater than current Scan Date.

Here's my solution.

Most Recent Body Mass =
MAXX (
    FILTER (
        ALL ( 'TESTS' ),
        'TESTS'[Date]
            = MINX (
                FILTER ( ALL ( 'TESTS' ), 'TESTS'[Date] >= MAX ( 'SPARTA_SCANS2'[Date] ) ),
                'TESTS'[Date]
            )
    ),
    'TESTS'[Bodyweight]
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

You could create a measure like

Most Recent Body Mass =
var currentAthlete = SELECTEDVALUE('Scans'[Athlete])
var currentDate = SELECTEDVALUE('Scans'[Date])
return SELECTCOLUMNS(
CALCULATETABLE( TOPN( 1, 'Tests', 'Tests'[Date]),
'Tests'[Athlete] = currentAthlete && 'Tests'[Date] <= currentDate),
"@val", 'Tests'[Body weight]
)