Forum Discussion

nbufff's avatar
nbufff
Icon for Helper I rankHelper 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~

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
1 year ago

Hi 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. 

nbufff's avatar
nbufff
Icon for Helper I rankHelper I
1 year ago

Yes, 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? 

DateSKUQtyStockIndexCheck_ColumnLogicLogic sample
11-NovA1050140if Previous row value on Check_Column with same Date and Same SKU=empty, Check_Column value=Stock-Qty=50-10=40
11-NovA20 220if 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-NovA30 3-10if 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-NovA40 4-40if Previous row value on Check_Column with same Date and Same SKU<=0,  Check_Column value=Qty-40