Return value based on the latest sales price

[dwhittaker1]

The table below contains an ItemID with multiple sales prices. I would like to create a measure that only shows me the last sales price for an ItemID. For example the meausre would return a sales price oF 120 for ItemID A and it would return  a sales price of 90 for Item B.

ITEMID comes from the DIMITEM table

ITEMNAME comes from the DIMITEM table

PARTFAMILY comes from the DIMITEM table

SALESPRICE comes from the factsalesinvoice table

DATE comes from the DIMDATE table

ItemID	ItemName	Part Family	SalesPrice	Date
A	Tire	12	100	12/1/2024
A	Tire	12	105	12/10/2024
A	Tire	11	110	11/12/2024
A	Tire	12	120	12/23/2024
B	Road	15	95	1/1/2024
B	Road	14	90	1/12/2024

[Anonymous · Accepted solution]

Hi,

Thanks for the solutions darkniqht  and SamWiseOwl  offered and i want to offer some more information for user to refer to.

hello @dwhittaker1 , you can create a measure.

MEASURE =
VAR a =
    CALCULATE (
        MAX ( factsalesinvoice[Date] ),
        ALLSELECTED ( factsalesinvoice ),
        factsalesinvoice[ItemID] IN VALUES ( DIMITEM[ItemID] )
    )
RETURN
    CALCULATE (
        SUM ( factsalesinvoice[SalesPrice] ),
        FILTER ( DIMDATE, DIMDATE[Date] = a )
    )

Output

Best Regards!

Yolo Zhu

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

[darkniqht]

To create a measure that returns the latest sales price for each ItemID, you can use a DAX formula in Power BI or a similar tool. Here’s a simple way to do it:

1. Sort by Date: Ensure your sales data is sorted by date in descending order.

2. Create the Measure:

   DAX

LatestSalesPrice = 
CALCULATE(
    LASTNONBLANK(factsalesinvoice[SalesPrice], 1),
    FILTER(
        factsalesinvoice,
        factsalesinvoice[ItemID] = SELECTEDVALUE(DIMITEM[ItemID])
    )
)

This measure will give you the most recent sales price for each ItemID based on the latest date in your data. Just make sure to adjust the table and column names if they differ in your model!

[SamWiseOwl]

Hi @dwhittaker1 

Most Reccent Price =
var maxDate = Calculate(LastDate(DIMDATE[Date]), All(DimItem[PARTFAMILY]))

Return
Calculate( Max(factsalesinvoice[SALESPRICE]), DIMDATE[Date] = maxDate)

Give that a whirl 🙂

[dwhittaker1]

@SamWiseOwl  thanksfor the help but the measure is i identifying the latest sales price its just replicating the current sales price. Also I would like to exclude the date from my table visual