dax problem: subtract multiple values in same column based on DATE and filter and get final values

Hi, iamjoy

Already have a preliminary understanding of what you need, but you didn't give any indication of how to determine the qty value corresponding to a type of instock_qty. For example, how to get the value 15735. One more thing, why the qty of A240600046 and A24070041 are marked in green, they have different order_no, which is different from the other calculation logic.

Best Regards,
Yang
Community Support Team

Hi, iamjoy 

You can try following dax to achieve your need.

DAX:

sumQTY By OrderNo and type = 
CALCULATE(
    SUM('Table'[QTY]),
    FILTER(
        'Table',
        'Table'[type] = EARLIER('Table'[type]) 
        && 'Table'[order_no] = EARLIER('Table'[order_no])
        )
    )

Result 1 = 
VAR CurrentIndex = 'Table'[Index]
VAR NextIndex = CurrentIndex + 1
VAR CurrentOrderNo = 'Table'[order_no]
VAR CurrentDate = 'Table'[date]
VAR NextType =
    CALCULATE (
        MAX ( 'Table'[type] ),
        FILTER ( 'Table', 'Table'[Index] = NextIndex )
    )
VAR PreviousFinalUnshippedQty =
    CALCULATE (
        MAX ( 'Table'[sumQTY By OrderNo and type] ),
        FILTER ( 'Table', 'Table'[Index] = NextIndex )
    )
VAR _result1 = 
IF (
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[order_no] = CurrentOrderNo &&
            'Table'[date] = CurrentDate
        )
    ) > 1,
    CALCULATE (
        DIVIDE(SUM('Table'[QTY]),COUNTROWS(
        FILTER(
            'Table',
            'Table'[order_no] = CurrentOrderNo &&
            'Table'[date] = CurrentDate
        )
    )),
        FILTER(
            'Table',
            'Table'[order_no] = CurrentOrderNo &&
            'Table'[date] = CurrentDate
        )
    ),
    BLANK()
)
VAR _result2 =
    IF (
        CurrentOrderNo
            = CALCULATE (
                MAX ( 'Table'[order_no] ),
                FILTER ( 'Table', 'Table'[Index] = NextIndex )
            )
            && 'Table'[type] <> NextType,
        'Table'[sumQTY By OrderNo and type] - PreviousFinalUnshippedQty,
        _result1
    )

RETURN
    _result2


Result 2 = 
IF(
    'Table'[Result 1] = BLANK() ,
    'Table'[QTY]
)

final_unshipped_qty = 
VAR _qty = 'Table'[Result 2] + 'Table'[Result 1]
VAR _index = 'Table'[Index]
RETURN
IF(_index = 1,BLANK(),_qty)

New type = 
VAR _type = 
    SWITCH(
        'Table'[type],
        "schedule_ship_date","final_unshipped_qty",
        "instock_date","instock_qty"
    )
RETURN
IF(
    'Table'[final_unshipped_qty] <> 0,
    _type
)

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum