Forum Discussion

drivas771994's avatar
drivas771994
Helper II
4 years ago
Solved

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

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 NameTest DateTest ResultBodyweight Scan DateBodyweight in KG

Athlete A

1/1/2022

6012/28/202180
Athlete A2/1/2022702/3/202278
Athlete A3/1/2022803/2/202279
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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]
)
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.