Forum Discussion
Community ChampionOne Order Date with Multiple Shipment Dates
Community Champion
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, 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
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 Anonymous,
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