Forum Discussion

mussaenda's avatar
mussaenda
Icon for Community Champion rankCommunity Champion
7 years ago

One Order Date with Multiple Shipment Dates

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...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI mussaenda,

 

Please share some sample data for test to coding formula.

 

Regards,

Xiaoxin Sheng

  • mussaenda's avatar
    mussaenda
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi, Anonymous,

     

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

     

    Sales Table

     

    Shipment Table

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        mussaenda
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi, Anonymous

         

        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