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~

     

shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper 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