Forum Discussion
Post PartisanEstimated Delivery date based on cumulative count
learner03 OK, I simplified the equation and was able to eliminate the GENERATESERIES. Not sure why I didn't do it this way the first time. Try this one:
FIFO 2 = VAR __DaysToFulfill = 10 VAR __ItemCode = [Item Code] VAR __Date = [Date] VAR __QtyToFulfill = SUMX(FILTER('BackOrders',[Item Code] = __ItemCode && [Date] <= __Date),[Backorder Qty]) VAR __Table = FILTER('OpenPurchaseOrders',[Item Code] = __ItemCode) VAR __Table1 = ADDCOLUMNS( ADDCOLUMNS(__Table,"__tqty",SUMX(FILTER(__Table,[ETA W/H] <= EARLIER([ETA W/H])),[Quantity])), "__LoopCounter",[__tqty] - __QtyToFulfill ) VAR __TargetDate = MINX(FILTER(__Table1, [__LoopCounter] >= 0),[ETA W/H]) VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill) RETURN __FulfillmentDate
Community Championlearner03 I believe you want something like the following assuming FIFO for the back oders. PBIX is attached below signature, you can ignore Table2 and TableA, that was somebody else's problem. Basically, a standard DAX while loop.
Column =
VAR __DaysToFulfill = 10
VAR __ItemCode = [Item Code]
VAR __Date = [Date]
VAR __QtyToFulfill = SUMX(FILTER('BackOrders',[Item Code] = __ItemCode && [Date] <= __Date),[Backorder Qty])
VAR __Table =
ADDCOLUMNS(
SELECTCOLUMNS(
FILTER(ALL('OpenPurchaseOrders'),[Item Code] = __ItemCode),
"__date",'OpenPurchaseOrders'[ETA W/H],
"__qty",'OpenPurchaseOrders'[Quantity]
),
"__LoopCounter",[__qty] - __QtyToFulfill
)
VAR __TargetDate = MINX(FILTER(__Table, [__LoopCounter] >= 0),[__date])
VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill)
RETURN
__FulfillmentDate
learner03Greg_Deckler https://drive.google.com/file/d/1iLS6xW1GyQUB0SFk7QR6413YwKYcEAgl/view?usp=sharing
I used your formula in the attached pbix and I saw that there are few gaps in the new column that calculated delivery dates.
For example if I see Item Code "9013" in both the tables. The place where it is calculating till estimated date 15/2 is working correctly, after this it is going blank.
and in Open Purchase order table, I can see that there is more stock coming and it can fulfil the back order amount-
- Greg_Deckler
learner03 The blanks come in when there is not enough product coming in from purchase orders to fulfill the request. Therefore, no estimated delivery date. I will check the calculations to see where things are coming from.
- learner03
Another example is in the working86.pbix that you have attached.
If I look at item code 1507 in both the files-
The line in the new column where it givs 11/01/2022 as result is coming correctly and 15/04/2022,but after that it does not populates the dates correctly-
In open Purchase order table, if I see same item code 1507, I can see that on 5th April 12,500 Quantity is coming in Warehouse, so in back order table, in the new coloumn where it says 24/04/2022, I think there also it should be 15/04/2022 as the rest back orders can be fulfilled easily by 12,500 qty that is coming on 5th april.
- Greg_Deckler
learner03 OK, the issue was the sort order of the purchase orders coming in. So, needed more complex DAX to convert the rows to a sorted string and then back to a table. Such a pain but the only way I know in DAX to guarantee ordering. So, the same while loop, just added code for the conversion to a sorted string and then back to a table. Updated PBIX attached.
Column = VAR __DaysToFulfill = 10 VAR __ItemCode = [Item Code] VAR __Date = [Date] VAR __QtyToFulfill = SUMX(FILTER('BackOrders',[Item Code] = __ItemCode && [Date] <= __Date),[Backorder Qty]) VAR __SortedString = CONCATENATEX(FILTER(ALL('OpenPurchaseOrders'),[Item Code] = __ItemCode), [ETA W/H] & ":" & [Quantity], "|", [ETA W/H]) VAR __Table = ADDCOLUMNS( ADDCOLUMNS( GENERATESERIES(1, PATHLENGTH(__SortedString),1), "__row",PATHITEM(__SortedString,[Value]) ), "__date", VAR __Colon = FIND(":", [__row],) RETURN DATEVALUE(LEFT([__row],__Colon -1)), "__qty", VAR __Colon = FIND(":", [__row],) VAR __Len = LEN([__row]) RETURN VALUE(RIGHT([__row], __Len - __Colon )) ) VAR __Table1 = ADDCOLUMNS( ADDCOLUMNS(__Table,"__tqty",SUMX(FILTER(__Table,[__date] <= EARLIER([__date])),[__qty])), "__LoopCounter",[__tqty] - __QtyToFulfill ) VAR __TargetDate = MINX(FILTER(__Table1, [__LoopCounter] >= 0),[__date]) VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill) RETURN __FulfillmentDate- learner03
I used your formula in the attached link but I am getting this below error-
https://drive.google.com/file/d/1iLS6xW1GyQUB0SFk7QR6413YwKYcEAgl/view?usp=sharing
What does this mean?