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
Post PartisanAnother 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.
Community Championlearner03 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?
- Greg_Deckler
learner03 Hmm. Very odd. I downloaded the file and do not get that error in the Column. That said, I did see that same exact error with a specific model setup and was thinking of posting an issue to the Issues forum which I think I will do now. I will post a link to the issue here once I open it. In the mean time, I have attached a version of the PBIX file that includes FIFO, LIFO, Optimized for Most Orders and Optimized for Most Quantity columns and measures.
- learner03
Thanks Greg_Deckler
Is it possible for you to attach the file that I shared in the link in which you applied the same formula ad didn't get the error? I would like to see that if I download it at my end, then am I getting the same error or not.