topic Re: field corresponding Date from another table earlier than selected date in different table in DAX Commands and Tips

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

Eswar — Tue, 11 Mar 2025 07:29:37 GMT

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

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

bhanu_gautam — Tue, 11 Mar 2025 08:07:18 GMT

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]
)

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

Eswar — Tue, 11 Mar 2025 10:16:57 GMT

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

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

FarhanJeelani — Tue, 11 Mar 2025 11:44:23 GMT

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.

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

Eswar — Wed, 12 Mar 2025 07:13:59 GMT

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.

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

Eswar — Fri, 14 Mar 2025 06:48:08 GMT

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.

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

Anonymous — Wed, 30 Apr 2025 06:43:29 GMT

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.

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

Anonymous — Wed, 09 Jul 2025 09:10:59 GMT

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.

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

Anonymous — Thu, 15 May 2025 07:30:05 GMT

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!