DAX - match sales date with relevant promotion date for each product

Question

I have a common sales table where different products were sold on multiple days. I have a second table with product specific promotions, where products can have multiple promotions over time.

I want to match the relevant promotion from the promotions table to the sales table using a calculated column.

A promotion is relevant to a sales date until the following promotion happens and the product gets sold afterwards:

My current solution is a nested if-function and looks somewhat like this:

Since a product can have up to a dozen promotions: Is there a better way to do this using a calculated column?

Thanks in advance

Answer 1

If you can use a measure (if promo_date is only for presentation purpose):

Relevant_Promo = CALCULATE(max(Sheet2[promotion_date]), filter(ALL(Sheet2), Sheet2[promotion_date]<= SELECTEDVALUE(Sheet3[sale_date]) && SELECTEDVALUE(Sheet3[produkt]) = Sheet2[produkt] ))

OR Calculated Column:

Relevant_Promo_CCOL = CALCULATE(max(Sheet2[promotion_date]), filter(ALL(Sheet2), Sheet2[promotion_date]<= (Sheet3[sale_date]) && (Sheet3[produkt]) = Sheet2[produkt] ))

Where my Sheet2 = Promotion; Sheet3 = Sales