Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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])
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |