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

[iamjoy]

raw date is from A~D

I want to get final unshipping quantity and Cumulative quantity (L & M column)

but I can't find perfect solution to solve this problem

K5 = C2+C3(=35000) - C5( first time instock group by order no )

K7 = K5-C7( second time instock group by order no )

K6 = K7-C6( third time instock group by order no )

K4= K6-(C4+C8) ( foruth time instock group by order no and it has 2 times  "instock" in one day )

L4 = total scheduled_qty - total instock_qty (35000-22281)

order_no	date	QTY	type
A24040025	2024/5/13	30878	schedule_ship_date
A24040025	2024/12/31	4122	schedule_ship_date
A24040025	2024/6/19	1922	instock_date
A24040025	2024/5/3	4196	instock_date
A24040025	2024/5/20	2335	instock_date
A24040025	2024/5/15	15	instock_date
A24040025	2024/6/19	13813	instock_date
A24070109	2024/8/21	4000	schedule_ship_date
A24060046	2024/7/12	1323	schedule_ship_date
A24060046	2024/7/30	1	instock_date
A24060046	2024/7/29	1	instock_date
A24060046	2024/7/30	212	instock_date
A24060046	2024/7/19	1098	instock_date

[Anonymous · Accepted solution]

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

[Anonymous]

Hi, @iamjoy 

You can try the following dax to achieve your need.

DAX:

final_unshipped_qty = 
VAR CurrentIndex = 'Table'[Index]
VAR PreviousIndex = CurrentIndex - 1
VAR CurrentOrderNo = 'Table'[order_no]
VAR PreviousType =
    CALCULATE (
        MAX ( 'Table'[type] ),
        FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
    )
VAR PreviousFinalUnshippedQty =
    CALCULATE (
        MAX ( 'Table'[sumQTY By OrderNo and type] ),
        FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
    )
VAR _result1 =
    IF (
        CurrentOrderNo
            = CALCULATE (
                MAX ( 'Table'[order_no] ),
                FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
            )
            && 'Table'[type] <> PreviousType,
        PreviousFinalUnshippedQty - 'Table'[sumQTY By OrderNo and type],
        BLANK ()
    )
VAR _orderCount =
    CALCULATE (
        COUNT ( 'Table'[order_no] ),
        FILTER ( 'Table', 'Table'[order_no] = CurrentOrderNo )
    )
VAR _result2 =
    IF ( _orderCount = 1, CALCULATE ( MAX ( 'Table'[QTY] ) ) )
RETURN
    _result1 + _result2

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

[iamjoy]

thank you so much

but I need 'final_unshipped_qty'  and 'date' column like this

[Anonymous]

Hi, @iamjoy 

Do you mean to regenerate a table with only the dates marked yellow and the corresponding final_unshipped_qty values?

Best Regards,
Yang
Community Support Team

[iamjoy]

result BI table like this

so I think what i need from raw data to A21~D21 table is right? 

[Anonymous]

Hi, @iamjoy 

Sorry, still can't see what you need, does the table you want refer to all the data in rows 21-34 or rows 21, 22, 27, 28, 32.

Best Regards,
Yang
Community Support Team

[iamjoy]

sorry..result BI table can't be translated to English

does the table you want refer to all the data in rows 21-34 or rows 21, 22, 27, 28, 32.>> no, it's from all this table

and I think this way is more clearly?

result table is from A20:D33  and  A20:D33  is from A1:D17 (raw data)

I can't find the solution to turn raw data to A20:D33..

[Anonymous · Accepted solution]

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

[Anonymous · Accepted solution]

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

[iamjoy]

thank you for your reply

15735 is from C4+C8 (1922+13813, group by order_no and date, it's shipped on 6/19 )

and total instock qty group by order no , for example , A24040025 is  22281 , and  all schedule ship qty is 30878+4122=35000

so 35000-22281=12719 (as final_unshipped_qty, and date need to use max schedule_ship_date, so it belongs to December)

why the qty of A240600046 and A24070041 are marked in green, they have different order_no, which is different from the other calculation logic. >>>> It's all instocked in July (2182=213+1+1098+2+868), the result BI table will show instocked_qty and final_unshipped_qty by year/month

Similarly, we can get total instocked_qty in June is 15735, in May is 6546 (c23+c24+c25) and we don't have instocked_qty in Auguest.