cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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
1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Super User

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

Regular Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors