Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |