Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Price on a given date

Master Data Table is called ( "PriceMD") [SKU]    [Price]   [Customer]   [Price Effective From Date] 123               10                      1000               1 Jan 2020    123             10  ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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])

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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])