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?
| ShipTable |
|
ReceiptTable |
|
DateTable |
|
ItemTable |
|
OrderNumTable |
| Item |
|
Item |
|
Date |
|
Item |
|
OrderNum |
| OrderNum |
|
OrderNum |
|
|
|
|
|
|
| ShipDate |
|
ReceiptDate |
|
|
|
|
|
|
| Qty |
|
|
|
|
|
|
|
|
InTransit sample.pbix (121.3 KB)
Here is a basic example I put together like my larger model where we have a Ship Table and a Receipt Table. I am trying to calculate the In-Transit of material that shipped before or equal to the Month-End date I select and was not received yet or was received after the Month-End date.
-you may noticed I have no Receipt of Order 99 yet so that will help with making sure I see totals included where receipt has not occurred within the records as well as those received after.
What you need to use here to the ‘events in progress’ pattern or technique.
This will give you the amount which is considered in transit on a specific date.
Check out the tutorial here which covers this technique in a bit of detail
You need to solve this in the model with slightly different relationships (inactive ones) then a specific formula technique that works with these unique relationships.
This should get you what you need I believe. See how you go
Sam
Good Day Sam,
Thank you for your response. I had viewed this video in the past and this is what I am attempting to work towards now.
My different Dates though are on different tables and since 1 is SQL Server but the other is Excel, I can’t make a simple join. To make things complicated, the Excel table which holds my Received Dates, has some instances of multiple dates for the same Order and Product.
Right now i’m working on using Query Editor to try and create a Unique instance of each Order#/Product# combination with the first date for that combination. This will allow me to do a Merge onto my Sales table coming from the server and use this sort of DAX you reviewed in the video.
I had actually done this using DAX only some time last year, but I must have deleted or over-wrote that file that I cannot locate my original Measure calculating this In-Transit.
Andrew