Having trouble calculating order fulfillment with orders, on hand inventory and transit inventory

[carlacervonif]

I am working on a report that will forecast what items we have or will have in stock vs. items we don't have stock for. I have 3 files I'm working with:

1. Order Report

2. Inventory report

3. Transit report (with in and pre transit)

I also have a date table and an item table (list of all items the company sells). The order report has request date which is connected to the date table and ordered item connected to the item table. Likewise, the Transit report is connected with the date table (by schedule arrival date) and item table (by item). Inventory report is only connected to the item table because there is no date (everything is on hand). 
My issue is that when I create a table and add Date (from date table), Item (from Item table), Ordered Quantity, On Hand, and Transit, nothing shows up for transit even though I know there is inventory and it shows in Power Query. Please help.

[parry2k]

@carlacervonif I think I'm out of options without looking at it, everything seems to be in the right place. 

[parry2k]

@carlacervonif can you share the DAX expression for the measure calculations, especially for the transit table? It is getting a bit hard to figure out what could be wrong without looking at things. I hope you understand.

[carlacervonif]

I understand! Here are the DAX formulas for the measures I mentioned:

1.  Ordered Quantiy = SUM('Order Report'[Quantity])

2. Cumulative Quantity = CALCULATE([Ordered Quantity],FILTER(ALLSELECTED('Date Table'), 'Date Table'[Date Value] <= MAX('Date Table'[Date Value])))

3. On Hand Inventory = SUM('Inventory Report'[Qty])

4. Transit Quantity = SUM('Total Transit'[Quantity])

5. Cumulative Transit = CALCULATE([Transit Quantity],FILTER(ALLSELECTED('Date Table'), 'Date Table'[Date Value] <= MAX('Date Table'[Date Value])))

6. Running Value = [On Hand Inventory] + [Cumulative Transit] - [Cumulative Quantity]

I hope that helps more!

[parry2k]

@carlacervonif very hard to tell, are you using any measures? if yes, what is this measure? any filters on the visual? And also I assume these are single direction relationships.

[carlacervonif]

Yes, all relationships are single direction. I'm using an "Ordered Quantity" measure that is just the sum of the Ordered Quantity column, "Cumulative Quantity" which is the cumulative ordered quantity for a given item, "On Hand Inventory" which is just the sum of quantities in the inventory report, "Cumulative Transit" which is the cumulative transit quantities for each item, and "Running Value" which is On Hand Inventory + Cumulative Transit - Cumulative Quantity. Let me know what you think!

[parry2k]

@carlacervonif can you confirm the date column purely contains the date value, not a time value? If it has time value then remove the time from this column (keep it purely a date value column)

[carlacervonif]

I can confirm that the date column is only date, not time!

[carlacervonif]

Thank you for your response! What would you suggest I do in regards to the Transit table?

[parry2k]

@carlacervonif You explained your question very well, Kudos to you! Based on the information you shared, it should work. Check if the date column in the Transit table has a time value in it, make sure to strip off the time if it is there, and then check. Seems like the relationship between the date dimension and the transit table is not working.