Create a Collum Based on another Table

[Anonymous]

I have two tables:
Table 1 have a collunm production-date.
       

DATEPRODUCTION	PRODUCTION
2023-06-12	3

2
In Table 2, i have 3 collunms, the first collum is the round, the second collunm is the round start date, and the third is the round finish date.

ROUND	START_DATE	END_DATE
2	2023/06/12	2023/06/28
3	2023/06/29	2023/07/12

I want to create a collunm in the Table 1, that have the round  where that event occurs. 

DATEPRODUCTION	PRODUCTION	ROUND
2023-06-12	3	2

How to compare these date columns and understand that something happened between the start date and the end date, and return the round number where this event occurred?/

[Adescrit · Accepted solution]

Hi @Anonymous 

Please try the following DAX in a new column in table 1

ROUND = 
VAR __DATE = Table1[DATEPRODUCTION]
RETURN
    CALCULATE( MIN( Table2[ROUND] ),
        __DATE >= Table2[START_DATE] &&
            __DATE <= Table2[END_DATE]
    )

[Anonymous]

it work exactly like i wanted, thank you a lot!!!