Calculated Column Based on two tables

[botaac]

I have two tables:

Table 1

Name	Email
John Doe	John.doe@email.com
John Doe	John.doe@email.com
Larry Fitz	Larry.Fitz@email.com
John Elway	John.elway@email.com
John Elway	John.elway@email.com
John Elway	John.elway@email.com

Table 2

Name	Email	Date Submitted
John Elway	John.elway@email.com	1/1/2023
Joe Burrow	Joe.burrow@email.com	5/1/2022
Larry Fitz	Larry.Fitz@email.com	12/1/2022
Brian Urlacher	brian.urlacher@email.com	9/1/2022

I want to create a new column using DAX on Table 1 called "Eligibility" where:

IF the inidivudla has a submission on Table 2 from the past 3 months, it says "Yes", other wise "No".

Intended Result would be:

Name	Email	Eligibility
John Doe	John.doe@email.com	No
John Doe	John.doe@email.com	No
Larry Fitz	Larry.Fitz@email.com	Yes
John Elway	John.elway@email.com	Yes
John Elway	John.elway@email.com	Yes
John Elway	John.elway@email.com	Yes

Thank you in advance!

[v-binbinyu-msft · Accepted solution]

Hi @botaac ,

Please try to create a new column with below dax formula:

Eligibility =
VAR cur_date =
    TODAY ()
VAR _date =
    LOOKUPVALUE ( Table2[Date Submitted], Table2[Name], Table1[Name] )
VAR _val =
    DATEDIFF ( cur_date, _date, MONTH )
RETURN
    IF ( ISBLANK ( _date ), "No", IF ( _val > 3, "No", "Yes" ) )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @botaac 

try like:

Eligibility =

VAR _date =

LOOKUPVALUE(

    Table2[Date Submitted],

    Table2[Name],

    Table1[Name]

)

RETURN

IF(

    _date>=EDATE(TODAY(),-3),

    "Yes", "No"

)