Forum Discussion
Create DAX Measure to get data based on condition from two tables?
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
- 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])))
2 Replies
- 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])))