Formula Help

Hi Anonymous ,

We can use the following steps to meet your requirement.

1. Create an index column in Power Query Editor.

2. Then we can create two calculated column.

Column = 
VAR x =
    CALCULATE (
        SUM ( 'Table'[Compl OP Qty] ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
                && 'Table'[OrderID] = EARLIER ( 'Table'[OrderID] )
        )
    )
VAR y =
    IF ( ISBLANK ( x ), 0, x )
RETURN
    IF (
        'Table'[Compl OP Qty] = y,
        'Table'[Compl OP Qty],
        IF (
            'Table'[Compl OP Qty] < y,
            'Table'[Compl OP Qty],
            IF ( y = 0, 'Table'[Compl OP Qty], y )
        )
)

Column 2 = 
VAR x =
    CALCULATE (
        SUM ( 'Table'[Column] ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
                && 'Table'[OrderID] = EARLIER ( 'Table'[OrderID] )
       )
    )
var y = IF(ISBLANK(x),'Table'[Column],x)
return
ABS('Table'[Column]-y)        

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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