Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing Current Price to Previous PRice

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

DateProductList PriceQuantityGross sales
2021-01-01Product A10220
2021-01-01Product A10550
2021-01-01Product A10330
2021-01-01Product A10660
2021-01-02Product A11444
2021-01-03Product A11444
2021-01-04Product A11444
2021-01-05Product A12112
2021-04-05Product A12672
2021-04-05Product A12784
2021-07-08Product A12784

 

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's avatar
    Anonymous
    4 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This formula is a killer!

       

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

       

      Thanks!