Compare Price Gap Current vs Previous

[xuexi1890]

Hello,

I want to calcuate what's the cost down % every time the sales person make to a customer & product.

i need 3 measures, what's the previous price, what's the cost down %, and if this is the latest offer to this customer.

summary as such table 1, attached you can find the database.

thanks in advance for your great help!

Cheers

Nate

https://1drv.ms/u/s!Am-wyNUhKsP7gyHtdwhQyea3j9jF

[v-shex-msft · Accepted solution]

Hi @xuexi1890 ,

You can try to use following measures if it suitable for your requirement:

Previous price = 
VAR currDate =
    MAX ( 'DATASET'[Date] )
VAR prevD =
    CALCULATE (
        MAX ( 'DATASET'[Date] ),
        FILTER ( ALLSELECTED ( 'DATASET' ), [Date] < currDate ),
        VALUES ( 'DATASET'[Customer] ),
        VALUES ( 'DATASET'[Product] )
    )
RETURN
    CALCULATE (
        MAX ( 'DATASET'[Price] ),
        FILTER ( ALLSELECTED ( 'DATASET' ), [Date] = prevD ),
        VALUES ( 'DATASET'[Customer] ),
        VALUES ( 'DATASET'[Product] )
    )

Changes = 
VAR currDate =
    MAX ( 'DATASET'[Date] )
VAR prevD =
    CALCULATE (
        MAX ( 'DATASET'[Date] ),
        FILTER ( ALLSELECTED ( 'DATASET' ), [Date] < currDate ),
        VALUES ( 'DATASET'[Customer] ),
        VALUES ( 'DATASET'[Product] )
    )
VAR prevPrice =
    CALCULATE (
        MAX ( 'DATASET'[Price] ),
        FILTER ( ALLSELECTED ( 'DATASET' ), [Date] = prevD ),
        VALUES ( 'DATASET'[Customer] ),
        VALUES ( 'DATASET'[Product] )
    )
RETURN
    IF (
        prevD <> BLANK (),
        DIVIDE ( prevPrice - MAX ( 'DATASET'[Price] ), prevPrice ),
        0
    )

Last Order= 
VAR _lastDate =
    CALCULATE (
        MAX ( 'DATASET'[Date] ),
        ALLSELECTED ( 'DATASET' ),
        VALUES ( 'DATASET'[Customer] ),
        VALUES ( 'DATASET'[Product] )
    )
RETURN
    CALCULATE (
        MAX ( 'DATASET'[Reference] ),
        FILTER ( ALLSELECTED ( 'DATASET' ), [Date] = _lastDate ),
        VALUES ( 'DATASET'[Customer] ),
        VALUES ( 'DATASET'[Product] )
    )

Regards,

Xiaoxin Sheng