DAX formula to return most recent cost for SKU

[Tmk123]

Hi, 

I am trying to create a measure "Latest Cost" that takes the most recent cost for a SKU based on the Invoice Date.  

I am trying to find these values for the sample data below:

ABC123 = $30.20

XYZ123 = $50.20

BBB123 = $30.90

Supplier SKU	Invoice Date	Purchase Unit Price
ABC123	12/22/2022	$37.95
ABC123	1/16/2023	$34.95
ABC123	11/9/2023	$30.20
XYZ123	10/16/2023	$50.20
XYZ123	9/29/2023	$45.20
XYZ123	8/31/2023	$65.20
BBB123	8/25/2023	$30.90
BBB123	8/11/2023	$31.00

[amustafa · Accepted solution]

Adjust the table name in the following DAX Measure:

Latest Unit Price =

VAR MaxInvoiceDate = CALCULATE(MAX('SKU'[Invoice Date]), ALLEXCEPT('SKU', 'SKU'[Supplier SKU]))

RETURN

    CALCULATE(MAX('SKU'[Purchase Unit Price]), FILTER('SKU', 'SKU'[Invoice Date] = MaxInvoiceDate))

If I answered your question, please mark this thread as accepted.
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[ThxAlot · Accepted solution]

[Tmk123]

Thank you!!

[tamerj1]

Hi @Tmk123 
Please try

Latest Cost =
SUMX (
    VALUES ( 'Table'[Supplier SKU] ),
    MAXX (
        TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[Invoice Date] ),
        'Table'[Purchase Unit Price]
    )
)

[amustafa · Accepted solution]

Adjust the table name in the following DAX Measure:

Latest Unit Price =

VAR MaxInvoiceDate = CALCULATE(MAX('SKU'[Invoice Date]), ALLEXCEPT('SKU', 'SKU'[Supplier SKU]))

RETURN

    CALCULATE(MAX('SKU'[Purchase Unit Price]), FILTER('SKU', 'SKU'[Invoice Date] = MaxInvoiceDate))

If I answered your question, please mark this thread as accepted.
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[Tmk123]

Thanks so much!