Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Tmk123
Helper II
Helper II

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 SKUInvoice DatePurchase Unit Price
ABC12312/22/2022$37.95
ABC1231/16/2023$34.95
ABC12311/9/2023$30.20
XYZ12310/16/2023$50.20
XYZ1239/29/2023$45.20
XYZ1238/31/2023$65.20
BBB1238/25/2023$30.90
BBB1238/11/2023$31.00
2 ACCEPTED SOLUTIONS
amustafa
Solution Sage
Solution Sage

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/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

ThxAlot
Super User
Super User

ThxAlot_1-1704491486863.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



View solution in original post

5 REPLIES 5
ThxAlot
Super User
Super User

ThxAlot_1-1704491486863.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



Thank you!!

tamerj1
Super User
Super User

Hi @Tmk123 
Please try

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

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/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks so much!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.