Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |