Forum Discussion

xuexi1890's avatar
xuexi1890
Icon for Helper I rankHelper I
7 years ago
Solved

Compare Price Gap Current vs Previous

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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