Measure Help - How to bring Quantity (by Order and Item) from another table based different date filters

DAX DAX Calculations
1

I’m having a tough time putting together a Measure that would give me an “InTransit” total for our Transfer Shipments at Month-End. What needs taken into consideration is the following facts:
-Transfers have to occur on or before the Selected Date
-Receipts will have occurred after the Selected Date (if the data was received from those facilities)
-or Receipts have not occurred yet (if no data was received yet from those facilities)

With that said, I am looking to add anything that works ( TRUE/FALSE or Received Quantity) that will allow me to apply this filter to what actually populates for Receipts.

‘Table.B.Receipts’[Column.B3.ReceiptDate] > DATE( 2018, 11, 30)

Tables/Columns that would be Involved are:

Table.A.Transfers
Column.A1.SalesOrder#
Column.A2.Customer#
Column.A3.ShipDate
Column.A4.Item#
Column.A5.TransferQuantity
Column.A6.SO_ITEM (concatenate of SalesOrder# & Item#)

Table.B.Receipts
Column.B1.SalesOrder#
Column.B2.Customer#
Column.B3.ReceiptDate
Column.B4.Item#
Column.B5.ReceivedQuantity
Column.B6.SO_ITEM (concatenate of SalesOrder# & Item#)

My current Measure is this, (ignore the filtering added unless you find that it needs moved around if using different Functions than I started with here; these filters are important because we have multiple warehouses and I need the functionality of the measure results changing per location and different date selections; I will be replacing these set dates and customer# with SELECTEDVALUE functions as they are on Slicers on the Report page)

**InTransit** = 
VAR vRecentTransfers = SELECTCOLUMNS( FILTER( 'Table.A.Transfers',
                        'Table.A.Transfers'[Column.A3.ShipDate] <= DATE( 2018, 11, 30) &&
                        'Table.A.Transfers'[Column.A3.ShipDate] > ( DATE( 2018, 11, 30) - 20 ) &&
                        'Table.A.Transfers'[Column.A2.Customer#] = 7525),
                        "Recent SO SKU", VALUES( 'Table.A.Transfers'[Column.A6.SO_ITEM] ) )
VAR vTransferQty = SUM( Column.A5.TransferQuantity )
RETURN
SUMX(
ADDCOLUMNS( vRecentTransfers,
            "Transfer_Qty", vTransferQty,
            "InTransit_Qty",  **HELP** ),
InTransit_Qty * 1)
2

Is it possible to see an example file here.

There a little bit going on and would require some testing to really understand all the variable at play.

Thanks
Sam

3

Just some other thoughts on this.

Are you using a date table here? This is essential for something like this.

Hardcoding dates like this isn’t a good idea, you should be trying to work with the natural context of the dates column inside of the dates table instead.

Also in combination with a dates table this logic should also be really done with a pattern like the below

CALCULATE( Measure,
FILTER( ALL( Dates), DatesColumn > ....some date..... ),
FILTER( ALL( Dates), DatesColumn < ....some date..... )

Something like this.

Another technique to review is the below. It’s slightly different but should give you some ideas.

4

Hi Sam,
Yes I am using a Date table. I only Hardcoded those dates and customer# for making this easier to understand. Those are actually replaced with SelectedValues of a Date Slicer for MonthEnd dates and a Slicer/Chiclet for the Warehouse Locations.

I’ll review your Sales and Orders video and get back to you on where I still stand with this.
Thanks for looking over this so far!