Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I would like to create a new column that would subtract the row values in 'FH' based on the 'Product'. The sample data set and corresponding expected output is as shown below. Kindly help.
Thanks.
Product | FH | Expected Outcome |
A | 2000 | 0 |
A | 5000 | 3000 |
A | 9000 | 4000 |
B | 2500 | 0 |
B | 6000 | 3500 |
B | 10000 | 4000 |
Try this
Output =
var _CurrentProduct=NewTable[Product]
var _PreviousFH= MAXX(FILTER(NewTable,NewTable[FH]<EARLIER(NewTable[FH]) && NewTable[Product]=_CurrentProduct),NewTable[FH])
var _result=NewTable[FH]-_PreviousFH
return IF(ISBLANK(_PreviousFH),0,_result)
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
@Zabeer , Try like a new column
Expected = [FH] - maxx(filter(table,[Product] = earlier([Product]) && [FH] < earlier([FH])),[FH])
Ideally, there should date to do this
Hi @Zabeer
try a column
Expected Outcome =
var _prevVal = CALCULATE(MAX('Table'[FH]),FILTER('Table','Table'[Product]=EARLIER('Table'[Product]) && 'Table'[FH] < EARLIER('Table'[FH])))
RETURN
IF(ISBLANK(_prevVal), 0, _prevVal)
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |