field corresponding Date from another table earlier than selected date in different table

[Eswar]

Hi,
I have two tables as below. I want a calculated column displaying the visit field in Table 2 based on date condition.
Condition: The Table1 (date) eariler than the Table2 (Date) by name. Please help me.

Table 1

Name	Visit	Date
A	W1	1-Jan-25
A	W2	10-Jan-25
A	W3	17-Jan-25
B	W1	3-Jan-25
B	W2	8-Jan-25
B	W3	15-Jan-25
C	W1	9-Jan-25
C	W2	16-Jan-25
C	W3	25-Jan-25

Table 2

Name	Event
A	15-Jan-25
B	7-Jan-25
C	30-Jan-25

Output with calculated column in Table 2 is expected as

Name	Event	Calculated column
A	15-Jan-25	W2
B	7-Jan-25	W1
C	30-Jan-25	W3

[Anonymous]

Hi @Eswar ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.
If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.
If you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[Anonymous]

Hi Eswar ,
May I ask of your issue has been resolved.If so,kindly share the solution or mark the answer that helped you.It will be halpful for the others in the community who may face similar issue.If not ,please follow with the below steps:
Please try this DAX for the calculated column in Table2:

CalculatedColumn =
VAR CurrentName = Table2[Name]
VAR CurrentEventDate = Table2[Event]
RETURN
MAXX(
TOPN(
1,
FILTER(
Table1,
Table1[Name] = CurrentName &&
Table1[Date] < CurrentEventDate
),
Table1[Date], DESC
),
Table1[Visit]
)

Thank you.

[Anonymous]

Hi @Eswar ,
Thank you @FarhanJeelani  and @bhanu_gautam for the helpful insights!
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

Regards,
Pallavi.

[bhanu_gautam]

@Eswar Create a calculated column in Table 2 using the following DAX formula:

dax
CalculatedColumn =
VAR CurrentName = Table2[Name]
VAR CurrentEventDate = Table2[Event]
RETURN
MAXX(
FILTER(
Table1,
Table1[Name] = CurrentName && Table1[Date] < CurrentEventDate
),
Table1[Visit]
)

[Eswar]

Hi Bhanu, Thank you. I asked a partial question. In the Table 2, if multiple records by the same name. 

Table1 Remains same.
Table 2

Name	RecordPosition	Event
A	1	15-Jan-25
B	1	7-Jan-25
C	1	30-Jan-25
A	2	9-Jan-25
A	3	23-Jan-25
B	2	14-Jan-25
B	3	13-Jan-25
C	2	10-jan-25
C	3	18-Jan-25

The output expected is 

Name	RecordPosition	Event	Calculated column
A	1	15-Jan-25	W2
B	1	7-Jan-25	W1
C	1	30-Jan-25	W3
A	2	9-Jan-25	W1
A	3	23-Jan-25	W3
B	2	17-Jan-25	W3
B	3	13-Jan-25	W1
C	2	10-jan-25	W1
C	3	18-Jan-25	W2

[FarhanJeelani]

Hi @Eswar ,

To handle multiple records for the same name in Table 2, you can modify the DAX formula slightly to account for each record's position while keeping the logic for matching dates and visits intact.

Here is the DAX formula for the Calculated Column in Table 2:

CalculatedColumn =
VAR CurrentName = Table2[Name]
VAR CurrentEventDate = Table2[Event]
VAR CurrentPosition = Table2[RecordPosition]
RETURN
MAXX(
FILTER(
Table1,
Table1[Name] = CurrentName &&
Table1[Date] < CurrentEventDate
),
Table1[Visit]
)

Please mark this post as solution if it helps you. Appreciate Kudos.

[Eswar]

Thank you, Farhan.
the provided DAX gives the out put 'visit' corresponding to min date by 'Name'. 
I will explain again. 
for example, Table 2 date 30-Jan-25 for A, all prior dates in Table 1 are (1-jan-25, 10-Jan-25 and 17-Jan-15). The first earlier date is  17-jan-25. The output in calculated column should be 'W3'.

The above provied DAX shows correspondong to lowest date 1-Jan-25, W1.

[Eswar]

Hi all,

Would you please help me this case? 

for example, Table 2 date 30-Jan-25 (looked up date) for A, all prior dates in Table 1 for A are (1-jan-25, 10-Jan-25 and 17-Jan-15). The first earlier date to looked up date is  17-jan-25. The output in calculated column expected is 'W3'.

The above provied DAX shows correspondong to lowest date 1-Jan-25, W1.