Pulling a value into another table based on the date being after the date in another table

[G0ggs]

Hello sorry for all the questions but you guys are so helpful! 

I would like to write a code that populates 'last check up' in table 1 with the date in table 2 based on the name of the footballer but only if the check up date is after the last game of football the player has played. 

Please let me know if there is a way to do this?

Thanks,

Gordon 

[v-yalanwu-msft · Accepted solution]

Hi @G0ggs  ,  

You could create a column by the following formula: 

Last check up =
VAR _date =
    CALCULATE (
        MAX ( 'Chek up dates'[Date] ),
        FILTER ( ALL ( 'Chek up dates' ), [Name] = EARLIER ( [Footballer name] ) )
    )
VAR _dif =
    DATEDIFF ( [Last time played football], _date, MONTH )
RETURN
    IF ( _date > [Last time played football] && _dif <= 6, _date )

The final output is shown below:  

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[G0ggs]

Hey it is showing "A table of multiple values was supplied where a single value was expected."

I have the names of the footballers multiple times and multiple times played football, does this cause an issue?

Thanks,

Gordon

[Fowmy]

@G0ggs 

Not sure how your model is organized. See my example below:

Table 02

Table 01

Compare with yours.

[G0ggs]

Hey Fowmy great thanks, ah sorry one other requirement is the date has to be within 6 months of the last time football played rather than just if the check up date is after the last time played, how do I add this in please?

[Fowmy]

@G0ggs 

As requested:

Lat Checkup Date = 
VAR __DATE =  LOOKUPVALUE(Table02[Date],Table02[Name],[Footballer name])
VAR _PLAYEDDATE = Table01[Last time played football]
VAR RESULT =  
    IF( 
        __DATE < _PLAYEDDATE && __DATE >= EDATE( _PLAYEDDATE , -6 ), __DATE)
RETURN
    RESULT

[Fowmy]

@G0ggs 

Add the following Column to your Table1:

Lat Checkup Date = 
VAR __DATE =  LOOKUPVALUE(Table02[Date],Table02[Name],[Footballer name])
VAR RESULT =  IF( __DATE > Table01[Last time played football], __DATE)
RETURN
    RESULT