cancel
Showing results for
Did you mean:
Helper I

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

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

1 ACCEPTED SOLUTION
Community Support

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 Tea
m_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Community Support

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 Tea
m_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

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

Super User IV

@G0ggs

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

Table 02

Table 01

Compare with yours.

Proud to be a Super User!

Helper I

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?

Super User IV

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

Proud to be a Super User!

Super User IV

@G0ggs

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

Proud to be a Super User!

Announcements