Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
8 |