Using Lookupvalue with date filters possible?

[cdawidow]

Hi guys, I am having some trouble using the LOOKUP DAX command as it is returning more than 1 value when I use it.  I have products that are sold under a contract name and I am trying to match the sales data to these contract names where possible.  The problem I am having however is that I have multiple contracts that have the same products tied to them but represent various years.  

For example:

CONTRACT NAMES                              PRODUCT                              EFFECTIVE DATE                                          EXPIRY DATE

DAY TO DAY NETS 2020/2021                    3                                      January 1 2020                                                Dec 31 2020

DAY TO DAY NETS 2019/2020                    3                                      January 1 2019                                                Dec 31 2019

DAY TO DAY NETS 2018/2019                    3                                     January 1 2018                                                 Dec 31 2018

Is there any way I can reference these effective and expiry dates to lookup the correct contract name based on specific products??

[wdx223_Daniel]

=VAR vProduct=MAX(Product[Product]) VAR vDate=MAX(Product[Date]) RETURN CALCULATE(MAX(Contracts[Contract Names]),FILTER(Contracts,Contracts[Product]=vProduct&&Contracts[Effective date]<=vDate&&Contracts[Expire date]>=vDate))

[DataInsights]

@cdawidow,

Would you be able to provide sample data for the table with sales data, as well as the expected result? Actual table and column names will make it easier to copy the solution into your pbix.