Estimated Delivery date based on cumulative count

[learner03]

I have 2 tables-

1) Open Purchase Order- this table shows when are the items arriving in the warehouse based on ETA W/H date. 

Date	Document Number	Shipment Header	Item Code	ETA W/H	Quantity
5/01/2022	230707	SHIP3909	12028	5/01/2022	5490
5/01/2022	230707	SHIP3909	12028	5/01/2022	126
6/01/2022	230722	SHIP3889	1093	24/01/2022	19000
6/01/2022	230722	SHIP3889	1093	24/01/2022	25000
6/01/2022	230722	SHIP3889	1093	24/01/2022	19800
24/01/2022	230842	SHIP3898	12085	24/01/2022	5800
24/01/2022	230842	SHIP3898	12085	24/01/2022	2000
28/07/2021	229160	SHIP3933	1507	1/02/2022	6125
19/08/2021	229417	SHIP3905	12085	8/02/2022	1000
19/08/2021	229417	SHIP3905	12085	8/02/2022	8000
25/08/2021	229479	SHIP4014	15275	14/02/2022	920
25/08/2021	229479	SHIP4014	15281	14/02/2022	1500
25/08/2021	229479	SHIP4014	15275	14/02/2022	80
6/09/2021	229601	SHIP3935	15275	15/02/2022	920
6/09/2021	229601	SHIP4012	15281	1/03/2022	1500
6/09/2021	229601		15275	15/02/2022	5360
6/09/2021	229601		15281	15/02/2022	3240
14/09/2021	229672	SHIP4012	15275	1/03/2022	4000
14/09/2021	229672	SHIP4012	15281	1/03/2022	3060
15/09/2021	229680	SHIP3991	1093	16/02/2022	5200
20/09/2021	229732		12028	25/02/2022	11214
19/10/2021	229962	SHIP3991	1093	16/02/2022	10000
19/10/2021	229967		15275	16/02/2022	4040
19/10/2021	229967		15281	16/02/2022	2940
21/10/2021	230009		1800	18/02/2022	500
19/08/2021	229417		12085	17/03/2022	9000
20/09/2021	229732		12028	18/03/2022	11520
19/10/2021	229962		1093	16/03/2022	4800
19/10/2021	229964		12085	16/03/2022	6000
19/10/2021	229971	SHIP4012	15275	1/03/2022	4040
19/10/2021	229971	SHIP4012	15281	1/03/2022	2940
9/11/2021	230135		15281	9/03/2022	1890
9/11/2021	230136		13516	9/03/2022	144
9/11/2021	230137		15281	9/03/2022	2910
18/11/2021	230254	SHIP4019	15673	19/02/2022	460
18/11/2021	230254		15673	18/03/2022	40
25/11/2021	230313		1093	25/03/2022	25800
25/11/2021	230324		1800	25/03/2022	1200
30/11/2021	230365		1093	30/03/2022	10000
30/11/2021	230371		15275	30/03/2022	2080
30/11/2021	230371		15281	30/03/2022	3060
25/11/2021	230309		12028	15/04/2022	24012
25/11/2021	230313		1093	14/04/2022	25800
25/11/2021	230320		12085	15/04/2022	8020
25/11/2021	230325		15281	15/04/2022	3000
25/11/2021	230326		15281	25/04/2022	3270
25/11/2021	230326		15275	25/04/2022	2720
25/11/2021	230326		1800	25/04/2022	1200
3/12/2021	230401		15275	2/04/2022	5000
6/12/2021	230426		1507	5/04/2022	12500
14/12/2021	230530		1507	13/04/2022	17250
14/12/2021	230535		1800	13/04/2022	3000
12/01/2022	230761		1093	12/05/2022	36000
12/01/2022	230763		12085	12/05/2022	10000
12/01/2022	230767		1507	12/05/2022	9000
13/01/2022	230780		1800	13/05/2022	1600
13/01/2022	230780		15275	13/05/2022	1520
13/01/2022	230780		15281	13/05/2022	3000
13/01/2022	230781		15281	13/05/2022	960

2) Back Orders- This table shows the quantity on back orders for the items based on Sales order create date.

Date	PO#	Sales Order #	Item Code	Ordered Qty	Invoiced Qty	% Delivered	Backorder Qty
7/12/2021	95260	SO00308414	1093	7000	0	0.00%	7000
10/12/2021	95375	SO00308638	1093	4800	0	0.00%	4800
20/12/2021	95590	SO00309028	1093	600	0	0.00%	600
20/12/2021	95599	SO00309032	1093	800	0	0.00%	800
21/12/2021	95541	SO00309078	1093	4000	2800	70.00%	1200
5/01/2022	95609	SO00309293	1093	3000	0	0.00%	3000
19/01/2022	95755	SO00309726	1093	4800	0	0.00%	4800
19/01/2022	95757	SO00309727	1093	600	0	0.00%	600
21/01/2022	95781	SO00309823	1093	3000	0	0.00%	3000
21/10/2021	94602	SO00306312	12028	1800	0	0.00%	1800
21/10/2021	94598	SO00306320	12028	4986	4972	100.00%	14
24/11/2021	95053	SO00307834	12028	40	0	0.00%	40
8/01/2022	95618	SO00309389	12028	10008	0	0.00%	10008
8/01/2022	95622	SO00309390	12028	3500	0	0.00%	3500
21/10/2021	94598	SO00306320	12085	1900	1740	92.00%	160
8/01/2022	95618	SO00309389	12085	3910	0	0.00%	3910
21/10/2021	94598	SO00306320	1507	2500	1250	50.00%	1250
29/10/2021	94672	SO00306637	1507	125	0	0.00%	125
29/10/2021	94673	SO00306642	1507	375	0	0.00%	375
11/11/2021	94820	SO00307164	1507	250	0	0.00%	250
24/11/2021	95057	SO00307826	1507	1500	0	0.00%	1500
24/11/2021	95053	SO00307834	1507	500	0	0.00%	500
1/12/2021	95201	SO00308128	1507	1250	0	0.00%	1250
3/12/2021	95230	SO00308293	1507	3000	0	0.00%	3000
20/12/2021	95599	SO00309032	1507	1250	0	0.00%	1250
5/01/2022	95610	SO00309292	1507	1250	0	0.00%	1250
8/01/2022	95618	SO00309389	1507	2500	0	0.00%	2500

I need another column in the back order table to calculate Estimated time of delivary based on the below calculation-

for example- if we take Item code "1507", 6125 Qty is arriving at 1st Feb 2022 (from open PO table), then when we see item "1507" in back order table and see the Back order qty column, this much quantity can be fulfilled for the dates between 21/10/2021 to 1/12/2021 as sum of the quantities for this duration will be 5250 (1250+125+375+250+1500+500+1250) and after than if we take another line for same product, it is 3000 and this will exceed 6125. So the estimated delivery date will be +10 working days from ETA W/H date. So if ETA W/H for this product is 1/2/2022, then The estimated delivery date will be approx 12/02/2022. The rest of the quantity for item code 1507 will be fulfilled by same process when another PO for 1507 arrives in the warehouse. 

This mechanism should go for all the products.

hope this makes sense.

@Samarth_18 @amitchandak  @Anonymous @ryan_mayu @parry2k @AlexisOlson 

[Greg_Deckler]

@learner03 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

[learner03]

@Greg_Deckler 

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]

@Greg_Deckler 

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.

[Greg_Deckler]

@learner03 Sure, here is the link to the issue: GENERATESERIES Bug? - Microsoft Power BI Community

And attached the PBIX that I downloaded.

[learner03]

@Greg_Deckler The file that you sent does has the old formula for the Column. It does not contain the new GENERATE series formula that you newly created. I need the fie in which you used the new formula at your end and didn't get any error as mine.Thanks

[Greg_Deckler · Accepted solution]

@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

[learner03]

@Greg_Deckler Thanks Greg. I tried this one and checked couple of items, it works great with them as with desired result. I will check the same with the new updated file as this data was a week old. I will let you know if any issue occurs. I am acception this as a solution.Thanks again.

[Greg_Deckler]

@learner03 Great to hear. If you need them, I revised the FIFO, LIFO and optimized columns and measures into FIFO 2, LIFO 2, etc. so you have 4 different ways of fulfilling backlog as both columns and measures without having to use GENERATESERIES. Attached PBIX.

[learner03]

@Greg_Deckler an extention to the report, how can I get the column next to the the new estimated delivery date where it can tell which associated Document number is used for the fulfilment of quantiy.

example-

In Backorder Table- the first few row are fulfilled by the first line of Open Puchase table. So, the associated Document number will be 229160 all the way through where 11/02/2022 and 230426 where 15/04 is in esti delivery column.

In Open Purchase table-

[Greg_Deckler]

@learner03 Two ways based on the latest code from email. This version returns the last purchase order used to fulfill the backorder:

Purchase Orders Used = 
    VAR __DaysToFulfill = 10
    VAR __ItemCode = [Item Code]
    VAR __Simba = [Simba Ref#]
    VAR __Date = [Date]
    VAR __QtyToFulfill = 
        SUMX(
            FILTER(
                'Backorders',
                [Item Code] = __ItemCode && 
                VALUE( TRUNC([Date]) & "." & RIGHT([Simba Ref#],LEN([Simba Ref#])-2)) <= VALUE( TRUNC(__Date) & "." & RIGHT(__Simba,LEN(__Simba)-2))
            ),
            [Backorder Qty]
        )
    VAR __Table = FILTER('On Purchase',[Item Code] = __ItemCode)
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(__Table,"__tqty",SUMX(FILTER(__Table,[ETA W/H] <= EARLIER([ETA W/H])),[Qty Balance])),
            "__LoopCounter",[__tqty] - __QtyToFulfill
        )
    VAR __TargetDate = MINX(FILTER(__Table1, [__LoopCounter] >= 0),[ETA W/H])
    VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill) 
RETURN
    MAXX(FILTER(__Table1,[ETA W/H] = __TargetDate),[Document Number])

Here is one that preserves history so to speak:

Purchase Orders Used 2 = 
    VAR __DaysToFulfill = 10
    VAR __ItemCode = [Item Code]
    VAR __Simba = [Simba Ref#]
    VAR __Date = [Date]
    VAR __QtyToFulfill = 
        SUMX(
            FILTER(
                'Backorders',
                [Item Code] = __ItemCode && 
                VALUE( TRUNC([Date]) & "." & RIGHT([Simba Ref#],LEN([Simba Ref#])-2)) <= VALUE( TRUNC(__Date) & "." & RIGHT(__Simba,LEN(__Simba)-2))
            ),
            [Backorder Qty]
        )
    VAR __Table = FILTER('On Purchase',[Item Code] = __ItemCode)
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(__Table,"__tqty",SUMX(FILTER(__Table,[ETA W/H] <= EARLIER([ETA W/H])),[Qty Balance])),
            "__LoopCounter",[__tqty] - __QtyToFulfill
        )
    VAR __TargetDate = MINX(FILTER(__Table1, [__LoopCounter] >= 0),[ETA W/H])
    VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill) 
RETURN
    CONCATENATEX(FILTER(__Table1,[ETA W/H] <= __TargetDate),[Document Number],", ")

[learner03]

@Greg_Deckler That's Brilliant !! Thanks again

[Greg_Deckler]

@learner03 Oh, sorry, I just opened your file and looked at Column and it did not have an error. Anyway, oddly enough, if I add a column with the new formula in it, I also get the error. The disturbing part is that your model and my model are identical other than table names, 2 disconnected tables. That makes it even more strange. There is something wrong with GENERATESERIES. See the Issue posting for more details but this just makes it completely weird. 

[learner03]

@Greg_Deckler 

I will keep seeing the post if someone respond to that. Meanwhile, in case if you find a solution at your end, Please do let me know. This is a very urgent project.

Thanks a lot for your efforts.

[learner03]

@Greg_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.