Last known value on related table

[marcos_osorio]

Hi, I have two tables, one with our current products (with product code) and another with the historic price of those codes.

For example:

Table A

P.Code   Actual Price

334353   34.3

Table B

Date              P.Code          Price

2/01/2019     334353          32.1

5/01/2020     334353          32.3

7/01/2020     334353          35.2

How do I obtain the last know price for the product?

Table A (new)

P.Code   Actual Price  Last Price

334353   34.3              35.2

I thought a possibility is making a summarized new table of Table B and relate this with Table A by P. Code but I didn't figure out how to do it.

Thank you very much in advance.

[V-lianl-msft · Accepted solution]

Hi @marcos_osorio ,

Try measure like these:

Measure 3 = var p = MAX('Table A'[P.Code])
return CALCULATE(LASTNONBLANKVALUE('Table C'[Date],MAX('Table C'[Price])),FILTER('Table C','Table C'[P.Code]=p))
OR
Measure = var p_code = MAX('Table A'[P.Code])
var lastest_date = CALCULATE(MAX('Table B'[Date]),ALLEXCEPT('Table B','Table B'[P.Code]))
return CALCULATE(MAX('Table B'[Price]),FILTER('Table B','Table B'[Date]=lastest_date&&'Table B'[P.Code]=p_code))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@marcos_osorio , A new column in Table 1

calculate(lastnonblankvalue(Table2[Date],Max(Table2[price])), filter(Table2, Table2[P.Code] = Table1[P.Code]))