Forum Discussion

nbufff's avatar
nbufff
Helper I
1 year ago
Solved

How to get same column previous row value

Dear all, I'm stuck in a situation to get value from previous row on same column. I want to add a column named "Check_Column" with logic and smaple provided below. Appreciate if you can advise a D...
  • xifeng_L's avatar
    xifeng_L
    1 year ago

    Hi nbufff 

     

    You can try below calculated column expression.

     

    Check Column = 
VAR Stock = CALCULATE(SUM('Table'[Stock]),ALLEXCEPT('Table','Table'[Date],'Table'[SKU]))
VAR Tbl = CALCULATETABLE('Table',ALLEXCEPT('Table','Table'[Date],'Table'[SKU]))
VAR CurIndex = 'Table'[Index]
VAR CurQty = 'Table'[Qty]
VAR CumQty = SUMX(FILTER(Tbl,'Table'[Index]<=CurIndex),'Table'[Qty])
RETURN
MAX(IF(CumQty-Stock<CurQty,MIN(CurQty,Stock-CumQty+CurQty))-CurQty,Stock-CumQty)

     

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

xifeng_L's avatar
xifeng_L
Super User
1 year ago

Hi nbufff 

 

You can try below calculated column expression.

 

Check Column = 
VAR Stock = CALCULATE(SUM('Table'[Stock]),ALLEXCEPT('Table','Table'[Date],'Table'[SKU]))
VAR Tbl = CALCULATETABLE('Table',ALLEXCEPT('Table','Table'[Date],'Table'[SKU]))
VAR CurIndex = 'Table'[Index]
VAR CurQty = 'Table'[Qty]
VAR CumQty = SUMX(FILTER(Tbl,'Table'[Index]<=CurIndex),'Table'[Qty])
RETURN
MAX(IF(CumQty-Stock<CurQty,MIN(CurQty,Stock-CumQty+CurQty))-CurQty,Stock-CumQty)

 

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~