Order Fulfillment 08-30-2021 14:18 PM - last edited 08-30-2021 20:44 PM

[Greg_Deckler]

OK, this one comes out of a specific request on the forums. The ask was for a DAX solution to determine the number of warehouses and names of the warehouses to fulfill sales order lines when specifically looking at fulfilling the orders from warehouses with the most inventory first. Thus, two tables, Sales Orders which has sales order lines by item and Inventory, which has item counts by warehouse. Now, the original ask was to treat each sales order as independent. This then morphed into a FIFO/LIFO situation. Below are the solutions implemented as measures. Could also be implemented as columns

Now, this is a rather unique implementation of Text to Table in that I use CONCATENATEX to return my warehouses in the order of the most inventory. Here though I am using a more performant version of "text to table" based on my Mythical DAX Index. This approach can be leveraged instead of the Text to Table approach. The new method is cleaner and more performant although it does suffer the limitation that complexity is added if your text contains pipe characters ( | ).

This is the measure for treating each sales order line as independent of one another:

Independent Orders = 
    VAR __ItemCode = MAX('SalesOrders'[ItemCode])
    VAR __QtyOrd = MAX('SalesOrders'[QtyOrd])
    VAR __Separator = ","
    VAR __Text = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],"|",[QtyStr],DESC)
    VAR __Count = PATHLENGTH(__Text)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Count,1),
                "__Word",PATHITEM(__Text,[Value],TEXT)
            ),
            "__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr])
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                __Table,
                "__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr])
            ),
            "__Decrement",[__RunningTotal] - __QtyOrd
        )
    VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value])
    VAR __Locations = COUNTROWS(FILTER(__Table1,[Value] <= __Index)) 
RETURN
    IF(ISBLANK(__Locations),COUNTROWS(DISTINCT('Inventory'[Location])),__Locations+0)

Independent Orders Locations = 
    VAR __ItemCode = MAX('SalesOrders'[ItemCode])
    VAR __QtyOrd = MAX('SalesOrders'[QtyOrd])
    VAR __Separator = ","
    VAR __Text = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],"|",[QtyStr],DESC)
    VAR __Count = PATHLENGTH(__Text)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Count,1),
                "__Word",PATHITEM(__Text,[Value],TEXT)
            ),
            "__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr])
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                __Table,
                "__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr])
            ),
            "__Decrement",[__RunningTotal] - __QtyOrd
        )
    VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value])
    VAR __Locations = FILTER(__Table1,[Value] <= __Index)
    VAR __Text1 = CONCATENATEX(__Table1,[__Word],",",[Value],ASC)
    VAR __Text2 = CONCATENATEX(__Locations,[__Word],",",[Value],ASC)
RETURN
    IF(ISBLANK(__Text2),__Text1,__Text2)

The FIFO versions on the first page are OK, they are additive according to the sales order number and thus a bit hard to read. With a few additional calculations we can clean this up.

FIFO Orders 2 = 
    VAR __ItemCode = MAX('SalesOrders'[ItemCode])
    VAR __OrderNo = MAX('SalesOrders'[OrderNo])
    VAR __QtyOrdPrevious = CALCULATE(SUM(SalesOrders[QtyOrd]),'SalesOrders'[OrderNo]<__OrderNo,'SalesOrders'[ItemCode] = __ItemCode)    
    VAR __QtyOrd = CALCULATE(SUM(SalesOrders[QtyOrd]),'SalesOrders'[OrderNo]<=__OrderNo,'SalesOrders'[ItemCode] = __ItemCode)
    VAR __Text = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],"|",[QtyStr],DESC)
    VAR __Count = PATHLENGTH(__Text)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Count,1),
                "__Word",PATHITEM(__Text,[Value],TEXT)
            ),
            "__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr])
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                __Table,
                "__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr])
            ),
            "__Decrement",[__RunningTotal] - __QtyOrd,
            "__DecrementPrevious",[__RunningTotal] - __QtyOrdPrevious
        )
    VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value])
    VAR __IndexPrevious = MINX(FILTER(__Table1,[__DecrementPrevious]>=0),[Value])
    VAR __Locations = COUNTROWS(FILTER(__Table1,[Value] <= __Index))
    VAR __LocationsPrevious = IF(ISBLANK(__QtyOrdPrevious),0,COUNTROWS(FILTER(__Table1,[Value] <= __IndexPrevious && [__DecrementPrevious]<=0)))
RETURN
    IF(ISBLANK(__Locations),0,__Locations - __LocationsPrevious + 0)

FIFO Orders Locations 2 = 
    VAR __ItemCode = MAX('SalesOrders'[ItemCode])
    VAR __OrderNo = MAX('SalesOrders'[OrderNo])
    VAR __QtyOrdPrevious = CALCULATE(SUM(SalesOrders[QtyOrd]),'SalesOrders'[OrderNo]<__OrderNo,'SalesOrders'[ItemCode] = __ItemCode)
    VAR __QtyOrd = CALCULATE(SUM(SalesOrders[QtyOrd]),'SalesOrders'[OrderNo]<=__OrderNo,'SalesOrders'[ItemCode] = __ItemCode)
    VAR __Text = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],"|",[QtyStr],DESC)
    VAR __Count = PATHLENGTH(__Text)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Count,1),
                "__Word",PATHITEM(__Text,[Value],TEXT)
            ),
            "__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr])
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                __Table,
                "__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr])
            ),
            "__Decrement",[__RunningTotal] - __QtyOrd,
            "__DecrementPrevious",[__RunningTotal] - __QtyOrdPrevious
        )
    VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value])
    VAR __IndexPrevious = MINX(FILTER(__Table1,[__DecrementPrevious]>=0),[Value])
    VAR __LocationsCurrent = FILTER(__Table1,[Value] <= __Index)
    VAR __LocationsPrevious = FILTER(__Table1,[Value] <= __IndexPrevious && [__DecrementPrevious]<=0)
    VAR __Locations = EXCEPT(__LocationsCurrent,__LocationsPrevious)
    VAR __Locations1 = __LocationsCurrent
    VAR __Text1 = CONCATENATEX(__Locations,[__Word],",",[Value],ASC)
    VAR __Text2 = CONCATENATEX(__Locations1,[__Word],",",[Value],ASC)
RETURN
    IF(ISBLANK(__QtyOrdPrevious),__Text2,__Text1)

LIFO is as easy as changing the < sign to > in __QtyOrdPrevious and __QtyOrd. Fulfilling out of warehouses with the least inventory, change the ASC to DESC in __Text. I have one example in the attached PBIX of LIFO.

Now, is it performant? Depends. The data model is built around 50,000 individual sales order lines, 5,000 items and 250,000 inventory rows in 50 different warehouses. If you render all sales order lines it takes about a minute on my meager little Surface. In Publish to Web it runs out of resources. However selecting an individual order with 5,000 lines returns in seconds. Individual items are sub-second. For scales beyond this, hopefully you are using your ERP system to tell you these kinds of things.

eyJrIjoiMTE3NWIyOGYtMDdmZS00YzMwLTgzYjQtYjNjODFlNjkyNWU1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[learner03]

@Greg_Deckler I have attached a part of the original file. Can you please help applying the same logic with FIFO structures according to the Sales order. So, Sales order number will be in ascending order, i.e example 111 will be fulfilled and then 112 and so on.

Sales order- has the list of orders

Sales order_lines- has item details and Qty of sales order

Bin_locations- All location in warehouse

Bin_items-has all items with respect to location.

[Greg_Deckler]

OK, @learner03 Here is your file back. Always more interesting working with more life-like data sets than I can easily mock-up on my own. I went with my original FIFO measures here because I ran into some issues with my "improved" FIFO due to partial order fulfillment from bins spanning multiple orders. I left the defaults for this on the interesting case of item 15276. In this case if fulfilling orders from the most stocked bins first, the first order is fulfilled by 2 ULDID's, 875738 and 1196659. The next order is small and it can be fulfilled by the remaining inventory in 1196659 so the next order shows the same two bins. The third order exhausts the inventory in 1196659 and so the remainder comes from 1228007.

So, this was the kind of messy display I was trying to avoid with FIFO 2 measures. But, it gets tricky keeping track of partial order fulfillment, where the current order is actually being fulfilled out of versus previous orders. I'm going to mull over this one and see if I can get a solid solution but my first take on this, my FIFO 2 measures in the original PBIX attached to this Quick Measure Gallery post has some issues dealing with it. My original thought was to get the current locations and the previous locations and do an EXCEPT. Except that if they are the same two bins that fulfill the sum of both orders, well then you get BLANK for the second order. So, back to the drawing board on that one! Could probably minimize this situation by fulfilling orders from the smallest bins first but not a real solution. I'm thinking that I can add a column to the virtual table that flags "partial bin fulfillment" and use that to adjust my locations returned on subsequent orders. I'll post an update here if I get it working but it won't be tonight/today.

Your updated PBIX is attached below where I have implemented the measures with some small tweaks required by your data. Overall, I was pretty happy with how it converted to your data actually, just mainly changing table/column names.

[Greg_Deckler]

@learner03 OK, I completely lied. I'm not sure who I thought I was kidding that I was going to be able to sleep tonight until I fixed the partial fulfillment problem. I did just that. Quite simple really, just an extra && filter when grabbing the previous fulfillment bins. OK, same scenario, the "2" versions of the measures present the information much more clearly. Same scenario with item 15276. The first sales order is fulfilled from the entire inventory in bin 875738 and partially the inventory in 1196659. The second sales order comes entirely from the remaining inventory in 1196659. The third sales order exhausts the remaining inventory in 1196659 and then also inventory in 1228007. Thus, the 2 version of the # of bins reads 2, 1, 2 for the sales orders and the 2 version of the ULDID locations only lists the exact bins that the sales order is fulfilled from. Victory.

Updated PBIX is attached with the new and improved "2" versions. 

And...good night!

[learner03]

Hi @Greg_Deckler 

I tried on my dataset and facing some issue related to where there are multiple items in a Sales order. So in the attached example- sleas order SO00302859 has 4 items to fulfil, but it is giving output as number of Bins as 1. When I looked at which bin it is looking at, so it is just looking at 1 item.

Similar with order number SO00304569, where it is showing 4 Bins but it has many items to fulfil as it should be around 20 Bins.

Can you please assist?

Thanks 

[learner03]

@Greg_Deckler Thanks for the help and effort. I will try this on orignal dataset and let you know how it goes.