Subtract the previous row with another column with multiple catagory

[ILANGO_10]

 i need the answer needed like if A1 is one of the catagory  material have totally 5 stocks and we needed two so atlast of A1 needed it's 3 like ... D2= C2-B2 ,,, D3= D2-B3... 

D4 = C4-B4 ,,, D5=D4 -B5 

Help me to find this column...

[v-zhangti · Accepted solution]

Hi, @ILANGO_10 

You can try the following methods. You need to add an index column to Power Query first.

Column:

Cumulative quantity = 
CALCULATE ( SUM ( 'Table'[quantity] ),
    FILTER ( 'Table' ,
        [material] = EARLIER ( 'Table'[material] )
        && [Index] <= EARLIER ( 'Table'[Index] )
    )
)

Result = [stock]-[Cumulative quantity]

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mahesh0016 · Accepted solution]

Create Calculated Column.
Needed =

VAR Sub =

    CALCULATE (

        SUM ( 'Subtract Table'[Quantity] ),

        FILTER (

            'Subtract Table',

            'Subtract Table'[Material] = EARLIER ( 'Subtract Table'[Material] )

                && 'Subtract Table'[Index] <= EARLIER ( 'Subtract Table'[Index] )

        )

    )

RETURN

    [Total Stock] - Sub

@ILANGO_10 i hope this help you!

[ILANGO_10]

Thank you so much sir... It means a lot..

[v-zhangti · Accepted solution]

Hi, @ILANGO_10 

You can try the following methods. You need to add an index column to Power Query first.

Column:

Cumulative quantity = 
CALCULATE ( SUM ( 'Table'[quantity] ),
    FILTER ( 'Table' ,
        [material] = EARLIER ( 'Table'[material] )
        && [Index] <= EARLIER ( 'Table'[Index] )
    )
)

Result = [stock]-[Cumulative quantity]

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ILANGO_10]

Thank you so much sir...