cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
G0ggs
Helper I
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?

 

G0ggs_0-1620498068279.png

 

Thanks,

Gordon 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
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:  

v-yalanwu-msft_0-1620727819725.png

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.
 

View solution in original post

6 REPLIES 6
v-yalanwu-msft
Community Support
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:  

v-yalanwu-msft_0-1620727819725.png

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.
 

View solution in original post

G0ggs
Helper I
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

Fowmy
Super User IV
Super User IV

@G0ggs 

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

Table 02

Fowmy_0-1620581251667.png


Table 01

Fowmy_1-1620581270338.png


Compare with yours.

 

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

G0ggs
Helper I
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?

 

Fowmy
Super User IV
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

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

Fowmy
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors