Associate Each Customer Sale With A Supplier Order ID (First In First Out Rule) RRS feed

  • Question

  • Hi,

    I have 2 Excel tables: an fSales table containing 1 column, "Sales", and an fOrders table containing 2 columns, "Order ID" and "Order Quantity".
    What I need is to associate in Power Query each Customer Sale with an Order Number.
    However, there will be situations where a Customer Sale is split between 2 Orders, and I need to determine how many units are coming from which order (this is why I have the last 4 columns in the fOrders table).

    A few mentions:
    a) The "Running Total Sales" and "Running Total Orders" Columns are not part of the Excel tables, but they can be calculated in Power Query using this approach:
    When I started thinking about this exercise, I was under the impression that I need the Running Totals, but maybe there's another approach without them.
    b) To solve this exercise, I've tried writing an Array formula in Excel instead of using Power Query, but it is painfully slow, as the fSales table is over 10,000 rows.
    c) Adding Calculated Columns in DAX might work, but it'd also be slow, plus I've been told that it's best to solve this in the ETL.
    d) For simplicity, there is only 1 SKU in here, but in reality, I have many SKUs in my tables.

    SKU        Sales      Running Total          (1) Units sold     (1) From Order    (2) Units Sold     (2) From Order

    SKU 1     219          219                           219                Order 1

    SKU 1    170           389                           31                 Order 1                          139                      Order 2

    SKU 1    374           763                           361                Order 2                         13                        Order 3

    SKU 1     137          900                           137                Order 3

    SKU 1     472          1,372                         472                Order 3

    SKU 1     433          1,805                         378                Order 3                         55                       Order 4

    SKU 1     115          1,920                         115                Order 4

    SKU 1     356          2,276                         356                Order 4

    SKU 1     369          2,645                         224                Order 4                         145              Order 5

    SKU 1       155          2,800                              155                    Order 5

    SKU Order ID          Order Quantity             Running Total Orders
    A         Order 1           250                              250 
    A         Order 2           500                              750  
    A         Order 3           1000                            1750   
    A         Order 4           750                              2500
    A         Order 5           500                              3000

    After having these 2 tables, we need to determine what orders are still in stock, and with how many units.
    We can see that there are 200 units (500-145-155) in stock from Order 5. 

    PS: The reason why I need this, is because it's part of a larger exercise where I want to calculate the Inventory (Orders - Sales) Cost, and to do that, the first step is to associate each Customer Sale with an Order ID.
    After this step, I need to calculate how many units are in stock from each order, because each order has a different cost/unit.

    Friday, January 22, 2016 7:12 PM


All replies