2 Relations between Date table, Master data and ProductionVolume Table

Question

Hi guys,&nbsp;&nbsp;I created this example to show you my problem:&nbsp;&nbsp;I have 3 tables:&nbsp;1. Date table&nbsp;&nbsp;2. Master data&nbsp;Products&nbsp; &nbsp; &nbsp;&nbsp;Producer&nbsp; &nbsp; &nbsp;&nbsp;ProductionStart&nbsp; &nbsp; &nbsp;&nbsp;ProductionEnd&nbsp; &nbsp; &nbsp;&nbsp;Item 1&nbsp; &nbsp; &nbsp;&nbsp;Producer A&nbsp; &nbsp; &nbsp;&nbsp;01.01.2022&nbsp; &nbsp; &nbsp;&nbsp;01.05.2022&nbsp; &nbsp; &nbsp;&nbsp;Item 1&nbsp; &nbsp; &nbsp;&nbsp;Producer B&nbsp; &nbsp; &nbsp;&nbsp;02.05.2022&nbsp; &nbsp; &nbsp;&nbsp;01.01.2099&nbsp; &nbsp; &nbsp;&nbsp;Item 2&nbsp; &nbsp; &nbsp;&nbsp;Producer C&nbsp; &nbsp; &nbsp;&nbsp;01.01.2022&nbsp; &nbsp; &nbsp;&nbsp;01.01.2099&nbsp; &nbsp; &nbsp;&nbsp;Item 3&nbsp; &nbsp; &nbsp;&nbsp;Producer C&nbsp; &nbsp; &nbsp;&nbsp;01.01.2022&nbsp; &nbsp; &nbsp;&nbsp;01.01.2099&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;3. Production data&nbsp;Production&nbsp; &nbsp; &nbsp;&nbsp;Date&nbsp; &nbsp; &nbsp;&nbsp;ProductionAmount&nbsp; &nbsp; &nbsp;&nbsp;Item 1&nbsp; &nbsp; &nbsp;&nbsp;01.01.2022&nbsp; &nbsp; &nbsp;&nbsp;50&nbsp; &nbsp; &nbsp;&nbsp;Item 1&nbsp; &nbsp; &nbsp;&nbsp;01.03.2022&nbsp; &nbsp; &nbsp;&nbsp;100&nbsp; &nbsp; &nbsp;&nbsp;Item 1&nbsp; &nbsp; &nbsp;&nbsp;01.06.2022&nbsp; &nbsp; &nbsp;&nbsp;100&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;So given that Item 1 has 2 different producers depending on the production Date I would like to get the following result:&nbsp;Product&nbsp; &nbsp; &nbsp;&nbsp;Date&nbsp; &nbsp; &nbsp;&nbsp;Production Amount&nbsp; &nbsp; &nbsp;&nbsp;Producer&nbsp; &nbsp; &nbsp;&nbsp;Item 101.01.2022&nbsp; &nbsp; &nbsp;&nbsp;50&nbsp; &nbsp; &nbsp;&nbsp;Producer A&nbsp; &nbsp; &nbsp;&nbsp;Item 1&nbsp;01.03.2022&nbsp; &nbsp; &nbsp; &nbsp;100&nbsp; &nbsp; &nbsp;&nbsp;Producer A&nbsp; &nbsp; &nbsp;&nbsp;Item 1&nbsp;01.06.2022&nbsp; &nbsp; &nbsp;&nbsp;100&nbsp; &nbsp; &nbsp;&nbsp;Producer B&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;There is Relation between Date - Production Data and a relation between Production Data&nbsp; and Master Data&nbsp;&nbsp;&nbsp;Would appreciate any help!! Thank you&nbsp;

amitchandak · Accepted Answer

iiomarioii , You can create a new column in the Production 
&nbsp;
maxx(filter(Master, Production [Product]&nbsp; =master([Product]) &amp;&amp; Production[Date] &gt;= master[ProductionStart] &amp;&amp;
Production[Date] &lt;= master[ProductionEnd]) , Master[Producer])

Forum Discussion

2 Relations between Date table, Master data and ProductionVolume Table

1 Reply

Related content

Filtercontext with date relation

Relation

Related employees via bridge

Using RELATED()

2 tables connected (related) by 2 tables

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

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

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