In-Transit Material

[AFoulk]

I am having a pain of a time getting a result that works for In-Transit products when I am viewing my Inventory Snap Shots for Month-Endings. Below are what I believe would be sufficient examples of Tables being used for this calculations.

I am using a Slicer to select the Month-Ending Dates and I want to know the Shipments in the ShipTable that all shipped = or < (on or before) the selected Month-Ending but the Receipt Dates in the ReceiptTable are > (after) the selected Month-Ending.

I keep trying different things with calculated columns to try and bring over the Receipt Date into the ShipTable and measures using AddColumns, SummarizeColumns, SelectColumns, and maybe some others, but all fail for many different reasons. Can someone please point me in the right direction?

factSHIP		factRECEIPT		dimDATE		dimIITEM		dimORDERNUM
Item		Item		Date		Item		OrderNum
OrderNum		OrderNum
ShipDate		ReceiptDate
Qty

[Greg_Deckler]

Sample data and expected output would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

It may may sense to merge your two fact tables if possible.

[AFoulk]

Hello @Greg_Deckler 

I mocked up a sample of the same scenario I am trying to do. I need to know the Quantity of what Shipped before my slicer's Month-Ending date, but was receipts After that date.

InTransit Sample

[AFoulk]

I made an adjustment to the file, but since the link is to the shared file on my GoogleDrive, you should be able to click that same link before.

I removed the Receipt record of Order 99 so this helps with calculating In-Transit to also include Shipped products that have no receipts recorded yet as well and not just what does have a receipt date that is after the Month-Ending date selected in the slicer.