Calculated Table

[Anonymous]

Hi, We have 2 tables and the requirement is we have to compare date from table 2 with date from table 1 and provide output as calculated table.  Thank you in advance.

Table 1 -

country	rate	date
MX	.5	03/10/2020
MX	.8	03/11/2020
US	.7	03/10/2020
US	.9	03/11/2020
MX	.8	03/20/2020
US	.7	03/20/2020

Table 2

Date

03/11/2020

Result Table -

country	rate	date
US	.9	03/11/2020
MX	.8	03/11/2020

[Anonymous]

NaturalinnerJoin on date column will work for you.

Or you can simply create one column in first table.

Column=Lookupvalue(table2[date],table2[Date],table1[date])

Filter out this column to is not blank.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[technolog · Accepted solution]

Option A. Filter with IN

Result =
FILTER(
Table1,
Table1[date] IN VALUES(Table2[Date])
)

This semijoins Table1 by the distinct dates in Table2. Works even if Table2 has many dates. No relationship needed.

Option B. Natural inner join by aligning column names

Result =
NATURALINNERJOIN(
Table1,
DISTINCT(
SELECTCOLUMNS(Table2, "date", Table2[Date])
)
)

SELECTCOLUMNS gives Table2 a column named date so the natural join can match it to Table1[date]. DISTINCT avoids duplicates from repeated dates in Table2.

Option C. Use TREATAS inside CALCULATETABLE

Result =
CALCULATETABLE(
Table1,
TREATAS(VALUES(Table2[Date]), Table1[date])
)

TREATAS applies the Table2 dates as a filter over Table1. It is very readable in larger models.