Forum Discussion
nbufff
Helper I
Helper IHow 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...
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~
FreemanZ
Super User
Super UserHi nbufff ,
it would be easier for DAX, if we see it in a different way:
20 = 50 - 10 - 20
-10 = 50 - 10 -20
-40 = 50 - 10 -20 - 30
if you have an index column, it would be very easy.
nbufffYes, there's index column. It's working on function "Earlier" but due to large data volume takes impact to system memory. Can we use window or offset instead?
Date SKU Qty Stock Index Check_Column Logic Logic sample 11-Nov A 10 50 1 40 if Previous row value on Check_Column with same Date and Same SKU=empty, Check_Column value=Stock-Qty =50-10=40 11-Nov A 20 2 20 if Previous row value on Check_Column with same Date and Same SKU>0, Check_Column value=Previous row value on Check_Column-Qty =40-20=20 11-Nov A 30 3 -10 if Previous row value on Check_Column with same Date and Same SKU>0, Check_Column value=Previous row value on Check_Column-Qty =20-30=-10 11-Nov A 40 4 -40 if Previous row value on Check_Column with same Date and Same SKU<=0, Check_Column value=Qty -40