Using Lookupvalue with date filters possible?

Question

Hi guys, I am having some trouble using the LOOKUP DAX command as it is returning more than 1 value when I use it.&nbsp; 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.&nbsp; The problem I am having however is that I have multiple contracts that have the same products tied to them but represent various years.&nbsp;&nbsp;&nbsp;For example:CONTRACT NAMES&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PRODUCT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EFFECTIVE DATE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EXPIRY DATEDAY TO DAY NETS 2020/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; January 1 2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Dec 31 2020DAY TO DAY NETS 2019/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; January 1 2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Dec 31 2019DAY TO DAY NETS 2018/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;January 1 2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Dec 31 2018&nbsp;Is there any way I can reference these effective and expiry dates to lookup the correct contract name based on specific products??

datainsights · Answer

cdawidow,&nbsp;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.

wdx223_daniel · Answer

=VAR vProduct=MAX(Product[Product]) VAR vDate=MAX(Product[Date]) RETURN CALCULATE(MAX(Contracts[Contract Names]),FILTER(Contracts,Contracts[Product]=vProduct&amp;&amp;Contracts[Effective date]&lt;=vDate&amp;&amp;Contracts[Expire date]&gt;=vDate))

Forum Discussion

Using Lookupvalue with date filters possible?

2 Replies

Related content

LookupValue with Filter

LookUpValue with Filter

lookupvalue filtering 2 columns

lookupvalue

lookupvalue PBI