Lookupvalue on date period

Question

Hi,&nbsp;I have been searching for ways to lookup a value (for instance a costprice) from another table based on a time interval.&nbsp;&nbsp;Sales Table&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; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PriceTableDate&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; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;StartDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EndDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cost2019-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&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; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019-01-31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;52019-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&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; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2019-01-31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;82019-01-02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&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; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019-02-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2019-02-28&nbsp; &nbsp; &nbsp; &nbsp; 72019-02-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&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; &nbsp; &nbsp;&nbsp;&nbsp;Desired outcomeSalesTableDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Price&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2019-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019-01-02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019-02-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp; &nbsp;&nbsp;I have no problems getting the price for the first of every month as those dates correspond with each other. But what I would like is for the sale on the second to lookup the corresponding cost price based on the price in the interval 2019-01-01 - 2019-01-31. How does one best set this up? Some use of minx/maxx? TopN?&nbsp;I managed to solve it by creating a helper table listing all dates between the intervall and adding the cost price on each date for each item. But this table grows quickly, and the sollution is not very neat. I am sure there are better and faster ways to do this.&nbsp;I don't mind if the sollution is given as a calculated column or a measure. Any way that is better than the list of all possible dates/items/costs would work.&nbsp;Thank you,Kind regards,Patrik&nbsp;

az38 · Accepted Answer

Hi&nbsp;Anonymous&nbsp;try new measure in the Sales tableMeasure = calculate(MAX('Price Table'[Cost]);
FILTER(ALL('Price Table');
'Price Table'[Product]=Selectedvalue('Sales Table'[Product]) &amp;&amp;
'Price Table'[StartDate]&lt;=selectedvalue('Sales Table'[Date]) &amp;&amp; 
'Price Table'[EndDate]&gt;=selectedvalue('Sales Table'[Date])
)
)do not hesitate to give a kudo to useful posts and mark solutions as solutionLinkedin

Forum Discussion

Lookupvalue on date period

2 Replies

Related content

Lookupvalue using dates?

lookupvalue

lookupvalue PBI

Custom Date Period Slicer To Get Last Quarter

Custom Date Period Selections