Reply
Frequent Visitor
Posts: 2
Registered: ‎11-27-2018
Accepted Solution

Price Change Indicator

Dears,

 

I have below table and would like to create a new column on in same prices column to show indicator for prices change increase/decrease comparing previous date prices.

 

ITEMIDPURCHUNITLatest INVENTDATECurrencyPRICEUNIT
PKG-021Pcs11/22/2018 0:00SAR             0.50
PKG-021Pcs11/01/2018 0:00SAR             0.50
PKG-021Pcs08/27/2018 0:00SAR             0.50
PKG-021Pcs11/26/2017 0:00SAR             0.50
PKG-021Pcs06/01/2017 0:00SAR             0.52
PKG-021Pcs03/22/2017 0:00SAR             0.51
PKG-021Pcs12/21/2016 0:00SAR             0.55
PKG-021Pcs12/14/2016 0:00SAR             0.55
PKG-021Pcs09/25/2016 0:00SAR             0.70
PKG-021Pcs02/10/2016 0:00SAR             0.57

Accepted Solutions
Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

Re: Price Change Indicator

Hi @shaikhshafiii

 

You may create a index column first. Then you may get the indicator with calculated column as below:

 

Index =
RANKX (
    Table1,
    CALCULATE (
        MIN ( Table1[Latest INVENTDATE] ),
        ALLEXCEPT ( Table1, Table1[PRICEUNIT] )
    ),
    ,
    ASC,
    DENSE
)
indicator =
VAR pre =
    CALCULATE (
        MAX ( Table1[PRICEUNIT] ),
        FILTER ( Table1, Table1[Index] = EARLIER ( Table1[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( pre ), BLANK (), Table1[PRICEUNIT] - pre )

 

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

Re: Price Change Indicator

Hi @shaikhshafiii

 

You may create a index column first. Then you may get the indicator with calculated column as below:

 

Index =
RANKX (
    Table1,
    CALCULATE (
        MIN ( Table1[Latest INVENTDATE] ),
        ALLEXCEPT ( Table1, Table1[PRICEUNIT] )
    ),
    ,
    ASC,
    DENSE
)
indicator =
VAR pre =
    CALCULATE (
        MAX ( Table1[PRICEUNIT] ),
        FILTER ( Table1, Table1[Index] = EARLIER ( Table1[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( pre ), BLANK (), Table1[PRICEUNIT] - pre )

 

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Posts: 3,919
Registered: ‎01-14-2017

Re: Price Change Indicator

Hi,

 

This calculated column formula should work

 

=[PRICEUNIT]-LOOKUPVALUE(Table1[PRICEUNIT],[Latest INVENTDATE],CALCULATE(MAX(Table1[Latest INVENTDATE]),FILTER(Table1,Table1[ITEMID]=EARLIER(Table1[ITEMID])&&Table1[Latest INVENTDATE]<EARLIER(Table1[Latest INVENTDATE]))),Table1[ITEMID],Table1[ITEMID])

 

Untitled.png