cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## DAX formula to return most recent cost for SKU

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
2 ACCEPTED SOLUTIONS
Super User

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

Proud to be a Super User!

Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

5 REPLIES 5
Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Helper I

Thank you!!

Super User

Hi @Tmk123

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

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

Proud to be a Super User!

Helper I

Thanks so much!

Announcements