Price on a given date

Question

Master Data Table is called ( "PriceMD")[SKU]&nbsp; &nbsp; [Price]&nbsp; &nbsp;[Customer]&nbsp; &nbsp;[Price Effective From Date]123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 Jan 2020&nbsp; &nbsp;123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 Jan 2021124&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 Jan 2021125&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 Jan 2021126&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 Jan 2021123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 Jan 2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;......&nbsp;&nbsp;Sales Table ("ConsolidatedSales")[SKU Sold]&nbsp; &nbsp; &nbsp;[Sold to Cust]&nbsp; &nbsp; &nbsp;[Sold On]&nbsp; &nbsp; &nbsp; &nbsp;[Price on Date]123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 Jan 2020&nbsp; &nbsp; &nbsp; ??126&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 Jan 2021&nbsp; &nbsp; ??......&nbsp;Can someone please guide me by writing a PQ to add a custom column called [Price on Date] calculated from master data.&nbsp;There are nearly 10 million rows in&nbsp; "ConsolidatedSales" . I am not sure on what will be the most effiencent way to handle the the task at hand.&nbsp;Thanks in advance.

amitchandak · Accepted Answer

Anonymous , Create a new column in ConsolidatedSales
&nbsp;
new column = var _date = maxx(filter(PriceMD,PriceMD[SKU] = ConsolidatedSales[SKU] &amp;&amp; PriceMD[Customer] = ConsolidatedSales[customer] &amp;&amp; PriceMD[Price Effective From Date] &lt;=ConsolidatedSales[Sold On] ),PriceMD[Price Effective From Date])return maxx(filter(PriceMD,PriceMD[SKU] = ConsolidatedSales[SKU] &amp;&amp; PriceMD[Customer] = ConsolidatedSales[customer] &amp;&amp; PriceMD[Price Effective From Date] =_max ),PriceMD[price])

Forum Discussion

Price on a given date

2 Replies

Related content

PBIRS Deployment Pricing

Reserved Fabric Pricing

Pricing in details

Pricing calculator

Find purchase price based on sales date (first price before date)