dax problem: how to calculate total instock qty group by date and get final unshipped qty

[iamJL]

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, @iamJL 

You can try the following methods.
Measure:

Final_unshipped_qty = 
Var _Sum1=CALCULATE(SUM('Table'[QTY]),FILTER(ALLEXCEPT('Table','Table'[order_no]),[type]="schedule_ship_date"))
Var _Sum2=CALCULATE(SUM('Table'[QTY]),FILTER(ALLEXCEPT('Table','Table'[order_no]),[type]="instock_date"))
RETURN
_Sum1-_Sum2

The rest of the calculations require you to elaborate on the logic.

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.

[lbendlin]

Could it be as easy as putting the data into a matrix visual?

[iamJL]

I tried.. if i turn raw data into  a matrix visual

and if i have a order no span different month, i get error value like picture (A24070041)

(please ignore the name of  order no 'TA' or 'A')

I should get  final unshippind qty '0' .... but it will show 870  , it should be instocked all quantity in July so it can't be count as unshipped.