DAX: Flag order if timestamp is in between 2 dates and product_id matches

[Jano]

Hey guys,

I want to provide some insight on performance of our deals section, so I got data looking like this:

Table A - order positions

order_id	timestamp	product_id
123	01.01.2021	123456789
456	04.01.2021	987654321
789	05.01.2021	123456789
321	08.01.2021	987654321
654	12.01.2021	345876112
987	15.01.2021	345876112

Table B - deal interval

deal_id	start_date	end_date	product_id
1	02.01.2021	04.01.2021	987654321
2	05.01.2021	05.01.2021	123456789

I'd like to map the deal_id from table B to the affected order_ids in table A.

In other words, check for each row in table A if table_A.timestamp is between (>= && <=) table_B.start_date and table_B.end_date and if table_A.product_id = table_B.product_id.

If theres a match, get the corresponding deal_id into the new calculated column in table A for further uses in the overall data model.

I tried different approaches with DAX statements in a calculated column but I'm not getting quite there..

I'd be greatful for every help on how to solve this 🙂

Best,

Jano

[amitchandak · Accepted solution]

@Jano , Create a new column in table 1

New column =
maxx(filter( Table2, Table1[product_id] = Table2[product_id] && Table2[start_date] <=Table1[Date] && Table2[end_date] <=Table1[Date] ), Table2[deal_id])

[Jano]

@amitchandak worked perfectly fine, thank you!