Price on a given date

[Anonymous]

Master Data Table is called ( "PriceMD")

[SKU]    [Price]   [Customer]   [Price Effective From Date]

123               10                      1000               1 Jan 2020   

123             10                     1000              1 Jan 2021

124              20                     1000             1 Jan 2021

125              21                    1000                1 Jan 2021

126               21                     2000                 1 Jan 2021

123               10                      1000               1 Jan 2020             

...

...

Sales Table ("ConsolidatedSales")

[SKU Sold]     [Sold to Cust]     [Sold On]       [Price on Date]

123                1000                    5 Jan 2020      ??

126                2000                    5 Jan 2021    ??

...

...

Can someone please guide me by writing a PQ to add a custom column called [Price on Date] calculated from master data.

There are nearly 10 million rows in  "ConsolidatedSales" . I am not sure on what will be the most effiencent way to handle the the task at hand.

Thanks in advance.

[Anonymous]

Many thanks Amit. This appears to be working.

I am assuming the _max variable is a typo and should be _date?

new column =
var _date = maxx(filter(PriceMD,PriceMD[SKU] = ConsolidatedSales[SKU] && PriceMD[Customer] = ConsolidatedSales[customer] && PriceMD[Price Effective From Date] <=ConsolidatedSales[Sold On] ),PriceMD[Price Effective From Date])
return
maxx(filter(PriceMD,PriceMD[SKU] = ConsolidatedSales[SKU] && PriceMD[Customer] = ConsolidatedSales[customer] && PriceMD[Price Effective From Date] =_max ),PriceMD[price])

[amitchandak · Accepted solution]

@Anonymous , Create a new column in ConsolidatedSales

new column =
var _date = maxx(filter(PriceMD,PriceMD[SKU] = ConsolidatedSales[SKU] && PriceMD[Customer] = ConsolidatedSales[customer] && PriceMD[Price Effective From Date] <=ConsolidatedSales[Sold On] ),PriceMD[Price Effective From Date])
return
maxx(filter(PriceMD,PriceMD[SKU] = ConsolidatedSales[SKU] && PriceMD[Customer] = ConsolidatedSales[customer] && PriceMD[Price Effective From Date] =_max ),PriceMD[price])