topic Re: Comparing Current Price to Previous PRice in Developer

Comparing Current Price to Previous PRice

Anonymous — Fri, 08 Oct 2021 08:54:33 GMT

Hi,

I need to compare any price changes in our purchase data per SKU. I've figured out to create a measure for the Current price, but need help to create a measure for Previouse price (=The last price before the price change)

Prices can change at any time. The SKU can have multiple rows with the same date, and it is not relevant to just pick the Price from the previous date, since the price probably changed a couple of months ago.

Here's an example of how the data is structured (obviously working with much larger data than this, thousends of products and data from +1 year back) :

Date	Product	List Price	Quantity	Gross sales
2021-01-01	Product A	10	2	20
2021-01-01	Product A	10	5	50
2021-01-01	Product A	10	3	30
2021-01-01	Product A	10	6	60
2021-01-02	Product A	11	4	44
2021-01-03	Product A	11	4	44
2021-01-04	Product A	11	4	44
2021-01-05	Product A	12	1	12
2021-04-05	Product A	12	6	72
2021-04-05	Product A	12	7	84
2021-07-08	Product A	12	7	84

So in this example I'd need to pick up:
Current price = 12
Previous price = 11

Do you have any good ideas of how I can make a measure for finding the previous price? 🙂

Thanks!

Re: Comparing Current Price to Previous PRice

Anonymous — Tue, 12 Oct 2021 03:16:12 GMT

Hi @Anonymous

My Sample:

Try measure codes as below.

Current Price = VAR _LastDate = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Product] = MAX ( 'Table'[Product] ) ), 'Table'[Date] ) VAR _CurrentPrice = CALCULATE ( MAX ( 'Table'[List Price] ), FILTER ( ALL ( 'Table' ), AND ( 'Table'[Product] = MAX ( 'Table'[Product] ), 'Table'[Date] = _LastDate ) ) ) RETURN _CurrentPrice

Previous Price = VAR _LastDate = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Product] = MAX ( 'Table'[Product] ) ), 'Table'[Date] ) VAR _PreviousDate = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), 'Table'[Product] = MAX ( 'Table'[Product] ) && 'Table'[Date] < _LastDate && 'Table'[List Price] <> [Current Price] ) ) VAR _PreviousPrice = CALCULATE ( MAX ( 'Table'[List Price] ), FILTER ( ALL ( 'Table' ), AND ( 'Table'[Product] = MAX ( 'Table'[Product] ), 'Table'[Date] = _PreviousDate ) ) ) RETURN _PreviousPrice

Then create a matrix add Product in Column field in Matrix and add measures in Value field. Then use Show on row function in Matrix format. Result is as below.

Best Regards,
Rico Zhou

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

Re: Comparing Current Price to Previous PRice

Anonymous — Thu, 14 Oct 2021 10:10:31 GMT

Hi!

Thank you for the help! With a few adjustments (had to add some more filters) it worked out on my more complex data as well. 🙂

Life saver!

/V

Re: Comparing Current Price to Previous PRice

Anonymous — Sat, 24 Jun 2023 13:40:20 GMT

This formula is a killer!

How would you change the dax to filter by vendor for the same product? @Anonymous

Thanks!