How to get same column previous row value

[nbufff]

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.

Date	SKU	Qty	Stock	Check_Column	Logic	Logic sample
11-Nov	A	10	50	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		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		-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		-40	if 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 · Accepted solution]

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]

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

[FreemanZ]

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]

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? 

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