One Order Date with Multiple Shipment Dates

Question

I am a newbie in power bi and I am dealing with some problems.&nbsp;I have 3 tables: Sales Table, Shipment Table, and Date Table.In Sales Table, I have the the Order Date, Job Number, the Qty of the Order.In Shipment Table, I have the Shipment Date, Job Number, the Qty of the Order shipped.&nbsp;Sales Table&nbsp;Shipment Table&nbsp;&nbsp;The scenario is, for some orders, there are multiple shipments made.For example:&nbsp;Job Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Order Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qty of Order &nbsp; &nbsp; &nbsp;&nbsp; Shipment Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qty of Order Shipped1180001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1/1/18 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1/25/18 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2/10/18 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2/15/18 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3/31/18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000&nbsp;My problem is I cannot merge the 2 tables because when I tried, the qty of the order is duplicating based on the number of rows of the shipment table via job number.&nbsp;What I needed is to calculate is the Open Quantity of Orders.&nbsp;Month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Job Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qty Open&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1180001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75,000&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1180001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1180001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;Also, I need to track all the open orders of the previous years by week/month.&nbsp;I tried to put the shipment date to the Sales Table and tried the formula below that I got here but it counts the qty as whole until reaching the final shipment date.&nbsp;General Orders 2 = 
CALCULATE (
    [Total Weight],
    FILTER (
        GENERATE (
            SUMMARIZE (
                CALCULATETABLE('LTC ME$Sales Line New', ALL('Date Table')), 
                'LTC ME$Sales Line New'[LTC ME$Sales Header.Order Date],
                'LTC ME$Sales Line New'[Shipment Date]
            ),
            DATESBETWEEN (
                'Date Table'[Date],
                'LTC ME$Sales Line New'[LTC ME$Sales Header.Order Date],
                'LTC ME$Sales Line New'[Shipment Date]
            )
        ),
        CONTAINS ( VALUES ( 'Date Table'[Date] ), [Date], 'Date Table'[Date] )
    ),
    
	CROSSFILTER('Date Table'[Date], 'LTC ME$Sales Line New'[LTC ME$Sales Header.Order Date], None)
)Total Weight is the Sum of Qty from Sales Table.&nbsp;I've been dealing with this for over a week now. I hope someone will help me solve this problem.&nbsp;Thank you,Mussaenda&nbsp;&nbsp;&nbsp;

anonymous · Answer

HI&nbsp;mussaenda,
&nbsp;
Please share some sample data for test to coding formula.
&nbsp;
Regards,
Xiaoxin Sheng

mussaenda · Answer

Hi, Anonymous,&nbsp;Here are the links of sample data. Thank you in advance!&nbsp;Sales Table&nbsp;Shipment Table

anonymous · Answer

Hi&nbsp;mussaenda&nbsp;,
&nbsp;
You can refer to following steps to create&nbsp;a matrix to achieve your requirement.
&nbsp;
Steps:
1. Create a calculate table with&nbsp;combined order no as bridge.
&nbsp;
Bridge =
DISTINCT (
    UNION ( VALUES ( Sales[Document No_] ), VALUES ( Shipment[Order No_] ) )
)

&nbsp;
&nbsp;
2. Build relationships from sale to bridge,&nbsp; shipment to bridge based on order no.
&nbsp;
3. Use above table fields to create matrix visual.

Regards,
Xiaoxin Sheng
&nbsp;

mussaenda · Answer

Hi, Anonymous&nbsp;Thank you for sharing your solution. Upon doing it, I noticed that you did not put the Qty ordered(from the Sales table) in the matrix. The main purpose of doing this is to deduct the Qty Base (Shipment Table) from Qty Base (Sales Table) to get the Outstanding/Open Qty with the dates involved.&nbsp;Thank you,Mussaenda

anonymous · Answer

Hi&nbsp;mussaenda&nbsp;,
You can add another 'qty base' to value fields, rename these fields based their table name.(sale qty, shipment qty)
After these steps, you can add a measure to get diff from two qty and group by current category. 
&nbsp;
It will display remain qty which you wanted.
&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

One Order Date with Multiple Shipment Dates

8 Replies

Related content

Sequential Order column based on multiple criteria

Shipment and order status tracking

Count Shipments with Multiple Sources

Multiple Values Column Order

V-Order & Z-Order