Price Change Indicator

[shaikhshafiii]

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.

ITEMID	PURCHUNIT	Latest INVENTDATE	Currency	PRICEUNIT
PKG-021	Pcs	11/22/2018 0:00	SAR	0.50
PKG-021	Pcs	11/01/2018 0:00	SAR	0.50
PKG-021	Pcs	08/27/2018 0:00	SAR	0.50
PKG-021	Pcs	11/26/2017 0:00	SAR	0.50
PKG-021	Pcs	06/01/2017 0:00	SAR	0.52
PKG-021	Pcs	03/22/2017 0:00	SAR	0.51
PKG-021	Pcs	12/21/2016 0:00	SAR	0.55
PKG-021	Pcs	12/14/2016 0:00	SAR	0.55
PKG-021	Pcs	09/25/2016 0:00	SAR	0.70
PKG-021	Pcs	02/10/2016 0:00	SAR	0.57

[Ashish_Mathur]

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

[v-cherch-msft · Accepted solution]

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 )

Regards,

Cherie