Get the cost I paid based on the date of sale with a measure

Question

I struggled to get this done, exhausted myself searching and couldn't find it, so I'll try my luck here.

I have two tables, the first contains the costs I paid for products with the dates. The second one I have my sales, with the code of the product that I sold, the date I sold it and the price I sold it for.

They look something like these two below:

TABLE A - COSTS

CODE    DATE          COSTPAID
A       02/07/2020    $2,50
B       19/07/2020    $12,50
B       10/08/2020    $13,50
A       26/08/2020    $3

TABLE B - SALES

CODE    DATE          PRICESOLD
A       05/07/2020    $5
A       28/08/2020    $5
B       25/07/2020    $25
B       16/08/2020    $25

I'd like to have a measure, or a column, that gave me the last cost I paid based on the date of the sale, like the example in the SALES table below:

CODE    DATE          PRICESOLD     COSTPAID
A       05/07/2020    $5            $2,5
A       28/08/2020    $5            $3
B       25/07/2020    $25           $12,5
B       16/08/2020    $25           $13,5

The closest I got was doing a column with the following code, but it overflowed:

LASTCOSTPAID =
CALCULATE (
    MAX ( COSTS[DATE] ),
    FILTER ( COSTS, COSTS[DATE] <= SALES[DATE] && COSTS[CODE] = SALES[CODE] )
)

Any help you could give me would be of great help.

Thanks in advance! :)

This may help: https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column — Alexis Olson, Oct 02 '20 at 14:22

score 0 · Answer 1 · answered Oct 03 '20 at 07:25

You where getting close. You will first need to retrieve the cost date of the date before the sales. Once you have found this lastCostDate, you can filter the table once more.

LastCostPaid = 
    var curCode = Sales[CODE]
    var curDate = Sales[DATE]
    var lastCostDate =  CALCULATE(MAX(Cost[DATE]), FILTER(Cost, curDate >= Cost[DATE] && curCode = Cost[CODE]))
return CALCULATE(SELECTEDVALUE(Cost[COSTPAID]), FILTER(Cost, curCode = Cost[CODE] && lastCostDate = Cost[DATE]))

score 0 · Answer 2 · answered Oct 04 '20 at 21:52

The main problem in the code you posted is that instead of the cost, you are getting the date.

To handle situations where more than one price was found for the same day, we can use MAX to get the highest price of the day. To find the last day, we can use the LASTDATE function inside a CALCULATETABLE to change the filter contest to contain only the dates up to the current SALES[DATE]. Then we also need a filter over the product CODE.

LASTCOSTPAID =
    CALCULATE(
        MAX( COSTS[COSTPAID] ),
        COSTS[CODE] = SALES[CODE],
        CALCULATETABLE(
            LASTDATE( COSTS[DATE] ),
            COSTS[DATE] <= SALES[DATE]
        )
    )

Get the cost I paid based on the date of sale with a measure

2 Answers2