One Order Date with Multiple Shipment Dates

[mussaenda]

I am a newbie in power bi and I am dealing with some problems.

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.

Sales Table

Shipment Table

The scenario is, for some orders, there are multiple shipments made.

For example:

Job Number        Order Date          Qty of Order        Shipment Date        Qty of Order Shipped

1180001               1/1/18                  100,000               1/25/18                    25,000

                                                                                     2/10/18                    25,000

                                                                                     2/15/18                    25,000

                                                                                     3/31/18                    25,000

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.

What I needed is to calculate is the Open Quantity of Orders.

Month       Job Number       Qty Open

   1               1180001          75,000

   2               1180001          25,000

   3               1180001            0

Also, I need to track all the open orders of the previous years by week/month.

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.

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.

I've been dealing with this for over a week now. I hope someone will help me solve this problem.

Thank you,

Mussaenda

[v-shex-msft]

HI @mussaenda,

Please share some sample data for test to coding formula.

Regards,

Xiaoxin Sheng

[mussaenda]

Hi, @v-shex-msft,

Here are the links of sample data. Thank you in advance!

Sales Table

Shipment Table

[v-shex-msft]

Hi @mussaenda ,

You can refer to following steps to create a matrix to achieve your requirement.

Steps:

1. Create a calculate table with combined order no as bridge.

Bridge =
DISTINCT (
    UNION ( VALUES ( Sales[Document No_] ), VALUES ( Shipment[Order No_] ) )
)

2. Build relationships from sale to bridge,  shipment to bridge based on order no.

3. Use above table fields to create matrix visual.

Regards,

Xiaoxin Sheng

[mussaenda]

Hi, @v-shex-msft

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.

Thank you,

Mussaenda

[v-shex-msft]

Hi @mussaenda ,

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.

It will display remain qty which you wanted.

Regards,

Xiaoxin Sheng

[mussaenda]

Hi @v-shex-msft,

Thank you for suggesting. I tried to add the order qty in the matrix but it is duplicating base on the rows of date. If you will enlighten me on grouping by current category, it will be a big help since I am a newbie. Bear with me.

See photo below, Thank you!

Warm Regards,

Mussaenda

[v-shex-msft]

Hi @mussaenda,

You can drag this column row fields after order date, then it will display as total amount of shipment qty.(I rename two fields as sole qty and shipment qty)

Regards,

Xiaoxin Sheng

[mussaenda]

Hi @v-shex-msft,

Maybe my questions were not clear. Sorry for that. I will post a clearer question. Thank you for answering my question here, you are brilliant.

Mussaenda