topic Re: dax problem: how to calculate total instock qty group by date and get final unshipped qty in DAX Commands and Tips

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

iamJL — Wed, 14 Aug 2024 06:59:21 GMT

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

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

lbendlin — Wed, 14 Aug 2024 15:12:50 GMT

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

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

iamJL — Thu, 15 Aug 2024 03:05:40 GMT

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.

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

Anonymous — Thu, 15 Aug 2024 08:24:38 GMT

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.