2 Relations between Date table, Master data and ProductionVolume Table

[iiomarioii]

Hi guys, 

I created this example to show you my problem: 

I have 3 tables:

1. Date table 

2. Master data 

Products	Producer	ProductionStart	ProductionEnd
Item 1	Producer A	01.01.2022	01.05.2022
Item 1	Producer B	02.05.2022	01.01.2099
Item 2	Producer C	01.01.2022	01.01.2099
Item 3	Producer C	01.01.2022	01.01.2099

3. Production data

Production	Date	ProductionAmount
Item 1	01.01.2022	50
Item 1	01.03.2022	100
Item 1	01.06.2022	100

So given that Item 1 has 2 different producers depending on the production Date I would like to get the following result: 

Product	Date	Production Amount	Producer
Item 1	01.01.2022	50	Producer A
Item 1	01.03.2022	100	Producer A
Item 1	01.06.2022	100	Producer B

There is Relation between Date - Production Data and a relation between Production Data  and Master Data 

Would appreciate any help!! Thank you 

[amitchandak · Accepted solution]

@iiomarioii , You can create a new column in the Production

maxx(filter(Master, Production [Product]  =master([Product]) && Production[Date] >= master[ProductionStart] &&

Production[Date] <= master[ProductionEnd]) , Master[Producer])