Using RELATED () with Date range conditions and many-to-many relationship

[JOO13]

Hi All, 

I'm trying to lookup some information from Table 1 below to Table 2. 

Table 1 - Sales info: 

Customer	Product	Sales	Contract Start Date	Contract End Date	Fiscal Year of Order Date
A	Z	100	1/1/2018	12/31/2018	2018
A	Y	200	1/1/2018	12/31/2018	2018
A	Z	200	4/1/2018	12/31/2018	2018
B	Y	400	9/1/2018	4/1/2019	2018
B	Y	300	1/1/2019	9/30/2020	2019
A	Z	150	10/1/2019	4/1/2020	2019

Table2 - Issues Info: 

Customer	Product	Total No. of Issues	Issue Date
A	Z	2	1/1/2018
A	Y	5	1/1/2018
A	Z	3	5/4/2018
B	Y	7	12/30/2018
B	Y	3	8/1/2020
A	Z	2	11/23/2019

Ultimately, we would like to have Table 2 looks like below with additional column lookup-ed from Table 1 (Fiscal Year of Order Date):

Customer	Product	Total No. of Issues	Issue Date	Fiscal Year of Issue Date
A	Z	2	1/1/2018	2018
A	Y	5	1/1/2018	2018
A	Z	3	5/4/2018	2018
B	Y	7	12/30/2018	2018
B	Y	3	8/1/2020	2019
A	Z	2	11/23/2019	2019

'Fiscal Year of Order Date' is match in Table 2 if and only if the Issue Date falls between the 'Contract Start Date' and 'Contract End Date'. 

I've tried to create additional column "Key" by concatenate "Customer" & "Product". Then I've linked them up between both tables as Many-to-many relationship. 

Below are the expressions that I have tried out by using the function RELATED(), 

Lookup = if(Table2[Key]<=related('Table1'[Key]),if(Table2[Issue Date].[Date]<=related('Table1'[Contract End Date].[Date]),if(Table2[Issue Date].[Date]>=related('Table1'[Contract Start Date]),related('Table1'[Fiscal Year of Issue Date]),"")))

but with below errors: 

"The column 'Table1'[Fiscal Year of Issue Date] either doesnt exist or doesn't have a relationship to any table available in the current context"

Hopefully anyone could shed some light here on what's wrong. Any other proposals are always welcome as well. 

Thank you. 

[Anonymous]

@JOO13 Create this calculated column in Issue Info table 

Column = 
VAR _issue = 'Issues Info'[Issue Date ]
VAR _result = CALCULATE(MAX('Sales Info'[Fiscal Year of Order Date ]),FILTER('Sales Info','Sales Info'[Contract Start Date ]<=_issue && 'Sales Info'[Contract End Date ]>=_issue && 'Sales Info'[Product] = 'Issues Info'[Product ]&&'Sales Info'[Customer]='Issues Info'[Customer ]))
RETURN _result

and check if it works for you 

[JOO13 · Accepted solution]

Thank you! @Anonymous 
This works well with slight modifications per below: 

Column =

VAR Prod = 'Issues Info'[Product]
VAR Cust = 'Sales Info'[Customer]

VAR _issue = 'Issues Info'[Issue Date ]
VAR _result = CALCULATE(MAX('Sales Info'[Fiscal Year of Order Date ]),FILTER('Sales Info','Sales Info'[Contract Start Date ]<=_issue && 'Sales Info'[Contract End Date ]>=_issue && 'Sales Info'[Product] = Prod &&'Sales Info'[Customer]= Cust))
RETURN _result