Calculating Same day shipping Metrics

[CaptRon]

Hi all,

I have two different data sources in one power bi report that i am looking to connect.  it is unfortunately a many to many relationship.  I am trying to find the % of lines shipped the same day and % of orders shipped the same day.  One of the tables has the order printed information and the other tabl

e has the order shipment information.  What is the best way to calculate this information? Each line is distinct by the order number and line number.  Is it possible to pull the shipment date from that table based on those two fields?  And create measures after that which calculated based on order date and shipment date matching?  Please help 🙂 

Pick Number	Order Number	Order Type	Line NUmber	Part Number	Order Date	Branch
101	1	Sales Order	1	part 1	1/1/2022
102	2	Sales Order	1	part 2	1/2/2022
103	3	Sales Order	1	part 3	1/3/2022
104	4	Sales Order	1	part 4	1/4/2022
105	5	Sales Order	1	part 5	1/5/2022

Pick Number	Order Number	Order Type	Line Number	Part Number	Ship Date	Branch
101	1	Sales Order	1	Part 1	1/1/2022
102	2	Sales Order	1	Part 2	1/4/2022
103	3	Sales Order	1	Part 3	1/5/2022
104	4	Sales Order	1	Part 4	1/6/2022
105	4	Sales Order	1	Part 5	1/7/2022

 Thank you!

[AilleryO]

Hi,

My suggestion would be to use Power Query to merge your tables, and bring back the ship date in your table of orders.

Then you can create a conditionnal conlumn to test if the value is the same in both columns to place a one or a zero on the lines of your table.

By summing up its column you'll get the count of shipment made the same day.

If you need more details about this solution pleace let me know qhat is not clear for you.

Hope it helps

[CaptRon]

Hi @AilleryO ,

Both of my tables are connected to power BI using an ODBC from our SQL database.  If i connect it to excel and use power query instead and connect Power BI to the power query will i still be able to refresh the data to give live information once the report has published?

[v-chenwuz-msft · Accepted solution]

Hi @CaptRon ,

Yes, you can use Append Query to combine these tables.

Append queries - Power Query | Microsoft Docs

And this will allow you to get the lasted data ( DirectQuery mode) as you said live information when you connect the data via using an ODBC from your SQL database.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AilleryO]

Hi,

So you can do the merge using DAX :

TabShipAndOrder DAX = 
VAR TableOrder = SELECTCOLUMNS(TabOrder, "OrderNum" , TabOrder[Order Number], "DateOrder" , TabOrder[Order Date]) //Choose columns you need in Order
VAR TableShipment = SELECTCOLUMNS( TabShipment , "ShipDate" , TabShipment[Ship Date] )
VAR MergeTable = GENERATEALL( TableOrder , TableShipment ) // To merge both table using FullOuter
RETURN
ADDCOLUMNS( MergeTable , "SameDayShipment_Test" , IF( [DateOrder] = [ShipDate] , 1 , 0) )

In both variables you can add columns as much as you need, just be careful not to have same name in both tables.

GENERATEALL is one of the "equivalent" of FullOuter merge in Power Query (along with GENERATE or CROSSJOIN).

Then you can sum up the column SameDayShipment_Test. 

Let us know if it works