Calculating total sales with changing sales price

[jmcph]

Hi,

I am trying to compute for the Sales amount (using Measures) with changing sales prices every 5 to 8 days. 

the relationship of my model looks like this : 

The value on my SalesBook are only Quantities, how do i multiply that with the respective sales price on the Masterlist_salesprice?

To further elaborate, sample of my data below:

Masterlist Sales Price

Product Code               Date                   Price

001                               1/1/21                10

001                               1/7/21                12

001                               1/16/21               8

SalesBook

Product Code               Date                   Qty

001                              1/3/21                10

001                              1/8/21                15

001                              1/15/21              20

My desired Sales Amount is 520. 

I hope you can help me with this. Thank you! 

[amitchandak · Accepted solution]

@jmcph , A new column in sales table

new column =
var _max = maxx(filter(masterlist, masterlist[Product Code] = sales[Product Code] && masterlist[Date] <= sales[Date]),masterlist[Date])
return
maxx(filter(masterlist, masterlist[Product Code] = sales[Product Code] && masterlist[Date] =_max),masterlist[Price]) * sales[Qty]

[ryan_mayu · Accepted solution]

@jmcph 

maybe you can try to create a column to get the price

Column = 
VAR _date=maxx(FILTER('Sales Price','Sales Price'[Date]<='SalesBook'[Date]&&SalesBook[Product Code]=SalesBook[Product Code]),'Sales Price'[Date])
RETURN MAXX(FILTER('Sales Price','Sales Price'[Product Code]=SalesBook[Product Code]&&'Sales Price'[Date]=_date),'Sales Price'[Price])

[amitchandak · Accepted solution]

@jmcph , A new column in sales table

new column =
var _max = maxx(filter(masterlist, masterlist[Product Code] = sales[Product Code] && masterlist[Date] <= sales[Date]),masterlist[Date])
return
maxx(filter(masterlist, masterlist[Product Code] = sales[Product Code] && masterlist[Date] =_max),masterlist[Price]) * sales[Qty]