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 DAX column to show data listed. I intend to use offset or window  but seemed failed.

 

DateSKUQtyStockCheck_ColumnLogicLogic sample
11-NovA105040if Previous row value on Check_Column with same Date and Same SKU=empty, Check_Column value=Stock-Qty=50-10=40
11-NovA20 20if 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 -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 -40if Previous row value on Check_Column with same Date and Same SKU<=0,  Check_Column value=Qty-40

 

xifeng_L , Is that possible to use Window funciton on that situation?Thanks. 

  • 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~

     

4 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi nbufff  Considering if SKU and Date changes, you will have a new stock value. Example like that:

     

    You would not be able to reference column which you are about to create. For your case, Check_Column, this column still not exist in your dataset, You just about to create it. Also considering you have a index column, dates and Skus are sorted accordingly. 

    You could try this code for above mentioned criteria:

     

    Check_Column = 
VAR CurrentRow = [Index]
VAR CurrentDate = [Date]
VAR CurrentSKU = [SKU]
VAR CurrentQty = [Qty]

VAR LastNonBlankStockIndex = 
    CALCULATE(
        MAX([Index]),
        FILTER(
            'Table',
            NOT(ISBLANK([Stock])) &&
            [Index] <= CurrentRow
        )
    )

VAR PreviousRow = 
    CALCULATE(
        MAX([Index]),
        FILTER(
            'Table',
            [Date] = CurrentDate &&
            [SKU] = CurrentSKU &&
            [Index] < CurrentRow
        )
    )

VAR PreviousQty = 
    CALCULATE(
        SUM([Qty]),
        FILTER(
            'Table',
            [Date] = CurrentDate &&
            [SKU] = CurrentSKU &&
            [Index] < CurrentRow
        )
    )

VAR _result = 
    IF(
        ISBLANK(PreviousRow),
        [Stock]-CurrentQty,
        IF(
            ISBLANK([Stock]),
            VAR _checkCond = CALCULATE(MAX([Stock]), FILTER('Table', 'Table'[Index] = LastNonBlankStockIndex)) - PreviousQty
            RETURN
            IF(
                _checkCond > 0,
                CALCULATE(MAX([Stock]), FILTER('Table', 'Table'[Index] = LastNonBlankStockIndex)) - CALCULATE(SUM('Table'[Qty]), FILTER('Table', [Date] = CurrentDate && [SKU] = CurrentSKU && [Index] <= CurrentRow)),
                -[Qty]
            )
        )

    )

RETURN
_result

     

     

    Output:

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

     

    Best Regards,
    Shahariar Hafiz

  • 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~

     

  • FreemanZ's avatar
    FreemanZ
    Super 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
      Helper 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