cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculated Column Based on two tables

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!

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

hi @botaac

try like:

Eligibility =
VAR _date =
LOOKUPVALUE(
Table2[Date Submitted],
Table2[Name],
Table1[Name]
)
RETURN
IF(
_date>=EDATE(TODAY(),-3),
"Yes", "No"
)