Comparing Current Price to Previous PRice

[Anonymous]

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! 

[Anonymous · Accepted solution]

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.

[Anonymous]

This formula is a killer!

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

Thanks!

[Anonymous]

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