Create DAX Measure to get data based on condition from two tables?

YasarKhan — Mon, 13 Jan 2020 07:53:57 GMT

Hello Experts,

I have a situation where I need to calculate quantity based on the condition of two date fields which are placed in two different tables.

I am trying to calculate the sum of delivered quantity(Table2-CustPackingSlipTrans) where Table2.delivery date(CustPackingSlipTrans.DeliveryDate) must be less than the Table1.requested ship date(SalesLine.RequestedShipDate).

With the above formula, I will be able to calculate the sales OTIF(OnTimeInFull).

Relationship: Table1.InventTransId(1):Table2.InventTransId(Many)

Table1 - Sales Line
InventTransId	ItemId	OrderedQty	Requested Ship Date
LOT-0001	ABC	10000	25-Dec-19

Table2 - CustPackingslipTrans
InvenTransId	Delivered Qty	DeliveryDate
LOT-0001	2000	12-Nov-19
LOT-0001	2500	01-Dec-19
LOT-0001	3500	31-Dec-19
LOT-0001	2000	02-Jan-19

I tried below the DAX query but without success.

Delivered Qty = CALCULATE(SUM('stage CUSTPACKINGSLIPTRANS'[QTY]))

OTIF = CALCULATE([Delivered Qty], FILTER('stage CUSTPACKINGSLIPTRANS', 'stage CUSTPACKINGSLIPTRANS'[DELIVERYDATE].[Date]<= 'stage SALESLINE’ [REQUESTEDSHIPDATE].[Date]))

Can anyone please help me on how to get the above result using DAX Custom Measure.

Many Thanks,

Yasar

Re: Create DAX Measure to get data based on condition from two tables?

Parisha — Mon, 13 Jan 2020 08:26:05 GMT

Try modifying your measure as below and check ... OTIF = CALCULATE(sum(CustPackingslipTrans[Delivered Qty]), FILTER('CUSTPACKINGSLIPTRANS', 'CUSTPACKINGSLIPTRANS'[DELIVERYDATE].[Date]<= RELATED('Sales Line'[Requested Ship Date].[Date])))

Re: Create DAX Measure to get data based on condition from two tables?

YasarKhan — Mon, 13 Jan 2020 09:16:33 GMT

Parisha Thanks a lot...It is working..great...I did put a lot of time on search and try and finally got the solution..Thanks

topic Re: Create DAX Measure to get data based on condition from two tables? in DAX Commands and Tips

Create DAX Measure to get data based on condition from two tables?

Re: Create DAX Measure to get data based on condition from two tables?

Re: Create DAX Measure to get data based on condition from two tables?