Forum Discussion

LasseL's avatar
LasseL
Helper I
7 years ago
Solved

Calculated Column looking up values in same table

Dear Experts,   This solution might be simple, but I am struggling a lot to work out how to make calculated column which is derived from values from same table.   The logic I am looking for is; I...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    LasseL Thanks for confirming that !!

     

    Here you go with the "ActiveOperation" column logic (You need to have an Index field added using "Power Query Editor" before creating the new column as below)

     

    ActiveOperation = 
VAR _PrevTotal = CALCULATE(SUM(Test115Flag[QtyToConsume]),FILTER(ALL(Test114Rnk),Test115Flag[ProdOrderNo]=EARLIER(Test115Flag[ProdOrderNo]) && Test115Flag[Index] < EARLIER(Test115Flag[Index]))) 
VAR _PrevRowVal = LOOKUPVALUE(Test115Flag[QtyToConsume],Test106CummSum[Index],Test115Flag[Index]-1)
RETURN IF(_PrevTotal>0 && _PrevRowVal = 0 && _PrevTotal = Test115Flag[QtyToConsume],1,0)

     

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

LasseL Thanks for confirming that !!

 

Here you go with the "ActiveOperation" column logic (You need to have an Index field added using "Power Query Editor" before creating the new column as below)

 

ActiveOperation = 
VAR _PrevTotal = CALCULATE(SUM(Test115Flag[QtyToConsume]),FILTER(ALL(Test114Rnk),Test115Flag[ProdOrderNo]=EARLIER(Test115Flag[ProdOrderNo]) && Test115Flag[Index] < EARLIER(Test115Flag[Index]))) 
VAR _PrevRowVal = LOOKUPVALUE(Test115Flag[QtyToConsume],Test106CummSum[Index],Test115Flag[Index]-1)
RETURN IF(_PrevTotal>0 && _PrevRowVal = 0 && _PrevTotal = Test115Flag[QtyToConsume],1,0)

 

LasseL's avatar
LasseL
Helper I
7 years ago

Great, I am struggling a bit getting the Index column to give the right order based on "Prod_orderNo" and LineNo, I'll continue to get that fixed and get back as soon as it is resolvedin order to test your calculation. Thanks.