Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Problem to forecast inventory levels with EDI orders

  • 1.  Problem to forecast inventory levels with EDI orders

    Posted Mar 29, 2019 10:07 AM
    Good day fellow PBI users,
    Here is the situation:

    I have the following tables

    • Date
    • Item
    • Open Purchase orders
    • Starting available inventory
    • MRP needs (explosion of ram materials needs generated by customer orders)
    • EDI minimum Quantity per Item

     

    My problem is that orders are not issued for EDI suppliers. Thus, a need triggered by MRP will, in real life, trigger a receipt of material (at a minimum qty) to be received by the needed date.

    When trying to forecast inventory levels at month-ends in the future, I am faced with these quantities that will be received but do not appear anywhere in my data. I need to consider the "to be received EDI quantity" in the rolling inventory forecast.

    What would be the best way to approach this phantom inventory to be received in power BI? The inventory to promissed solution published on this forum is very interesting gave me the clues for the rolling forecast, but I lack the solution for the above problem

    Thanks in advance for your help!

    Jacques



    ------------------------------
    Jacques Bruneau
    FINANCE AND I.T. DIRECTOR
    SHERBROOKE QC
    8195732331
    ------------------------------
    Power Summit (bi) - Post


  • 2.  RE: Problem to forecast inventory levels with EDI orders

    Posted Apr 01, 2019 04:10 AM
    Edited by Gopa Kumar Sivadasan Apr 01, 2019 04:11 AM
    Hi @Jacques Bruneau

    It will be helpful if you can upload some sample data. It can be in Excel and Date Table is not necessary. But other tables will be required to better understand the data and Table relationships. It will help the community to come up with solutions. ​

    ------------------------------
    Gopa Kumar S
    ------------------------------

    Power Summit (bi) - Post


  • 3.  RE: Problem to forecast inventory levels with EDI orders

    Posted Apr 10, 2019 12:32 PM
    Hello, Sorry to get back .. my accounting side was held by month-end !

    Here are the files with some sample data
    Inventory ( is always as of last day ) his is the opening quantities and value
    mrppeg-report : This is the MRP production need for parts
    Openpo : These are the open purchase orders for parts with the revised prom date as the target receiving date.
    MRP Share Items: These are the EDI parts. Normally there are no purchase orders for these but not always. IN some cases some parts will have a firm PO issued and other times only a quantity of 1 meaning that the EDI quanttity will prevail.

     Thus the problem is to determine the Quantities and value of inventory omer time ( month-ends) where some production needs can sometime not have a firm PO with a quantity and date expected but in most cases only show a purchse order with a  quantity of 1 and will most likely be backdated i.e. the MRP Share MPQ prevails and is to be expected by the needed mrppeg date.
    I am trying to follow qauntity and value of inventory :  Start inventory + Open PO items to be received - Mrppeg-report consumption+ EDi orders expected = inventory at end of period and the opening value for next period in the date range selected starting today().

    Hopefully the above makes sense. And thank you in advance for your help !

    Regads

    ------------------------------
    Jacques Bruneau
    FINANCE AND I.T. DIRECTOR
    SHERBROOKE QC

    ------------------------------

    Attachment(s)

    xlsx
    MRP Share Items.xlsx   13K 1 version
    xlsx
    mrppeg-report.xlsx   10K 1 version
    xlsx
    Inventory.xlsx   11K 1 version
    xlsx
    openpo.xlsx   11K 1 version
    Power Summit (bi) - Post


  • 4.  RE: Problem to forecast inventory levels with EDI orders

    Posted Apr 15, 2019 04:38 AM
    Hi @Jacques Bruneau

    What I understand is that for items in the 'MRP Share Items' table, entries in the 'OpenPo' table will sometimes have 1 as the value in [Qty Orig] and [Qty to Rcve] fields and at other times the value of that item in the 'MRP Share Items' table. However for your calculation, the values with 1 should be replaced with the value of that item in the 'MRP Share items' table. Is my understanding correct?

    If my understanding is correct, can't you have two new calculated columns (either through DAX or in Query Editor) to replace the [Qty Orig] and [Qty to Rcve] fields based on the condition that if the value in these fields is 1, then lookup the value from the 'MRP Share Items' table? You can then use these calculated columns in all your calculations instead of the original columns?



    ------------------------------
    Gopa Kumar S
    ------------------------------

    Power Summit (bi) - Post


  • 5.  RE: Problem to forecast inventory levels with EDI orders

    Posted Apr 15, 2019 11:05 AM
    Hello,
    Your understanding is right.
    I tought of using a calculated column in the Openpo , however, the date in Openpo is always sometime in the past when the original blanket order was placed, thus not good in a context calculation in any period. The needed quantity will be triggered by the mrppeg which is a "usage or need" by a certain date. In fact it is like I need to create a "ghost order" that will be added to the "net balance in stock" measure at an evaluation date, if the "net balance in stock" is negative, as if the material is to be received by that needed date at the roundup multiple of the missing quantity.
    If I summarize my thinking.
    I start with an opening inventory quantity ( closing quantity at opening)
    plus Open orders qty for items not an "MRP Share", if "MRP SHARE" then 0
    Less Qty Needed" as per the "mrppeg" file
    =net balance in stock
    if net balance in stock is  < 0 and item is an "MRP Share" item then EDI quantity (or "Ghost order") = the  roundup multiple of the missing quantity
    Net balance in stock plus EDI order if applicable = Closing inventory qty
    Closing inventory Value = Closing inventory qty x Price ( found in inventory file)
    and so on for the range of date selected.
    Hopefully I overcomplicate the whole thing and there is a simpler path to this.
    Thanks in advance
     Jacques

    ------------------------------
    Jacques Bruneau
    FINANCE AND I.T. DIRECTOR
    SHERBROOKE QC
    8195732331
    ------------------------------

    Power Summit (bi) - Post


  • 6.  RE: Problem to forecast inventory levels with EDI orders

    Posted Apr 16, 2019 03:50 AM
      |   view attached
    Hi @Jacques Bruneau

    Please see if the following helps you. This is based on my understanding as outlined below:

    1. For Non MRP Share items, Closing Inventory Qty = op qty for each month + Cumulative Order Qty in PO – Cumulative Qty needed as per mrppeg report.

    2. However, for MRP share items, the closing inventory is calculated using the following logic:

    I apologize if my understanding is wrong.

    Based on my above understanding the solution is:


    And the DAX code for the above is:

    Closing Inventory Qty = 
    -------------------------------------------------------
    --initial variables
    VAR rlvntEOM =
        SELECTEDVALUE ( 'Date Table'[End of Month] )
    VAR rlvntitem =
        SELECTEDVALUE ( 'Item Master'[ITEM NO] )
    VAR rlvntDateTable =
        FILTER ( ALL ( 'Date Table' ), 'Date Table'[Date] <= rlvntEOM )
    VAR MRPitems =
        VALUES ( 'MRP Share Items'[Item] )
    VAR MPQ =
        CALCULATE (
            MAX ( 'MRP Share Items'[MPQ] ),
            'MRP Share Items'[Item] = rlvntitem
        ) 
    -------------------------------------------------------
    --find the current qty on Hand
    VAR qtyonHand =
        CALCULATE ( SUM ( Inventory[QTY ON HAND] ), rlvntDateTable ) 
    -------------------------------------------------------
    --find the qty needed as per mrppeg Table
    VAR qtyNeeded =
        CALCULATE ( SUM ( 'MRPpeg-Report'[PEG QTY] ), rlvntDateTable ) 
    -------------------------------------------------------
    --find the open orders qty for non MRP share till end of month
    VAR nonmrpOpenOrders =
        CALCULATE (
            SUM ( OpenPO[Qty to Rcve] ),
            NOT ( OpenPO[Item] IN MRPitems ),
            rlvntDateTable
        ) 
    -------------------------------------------------------
    --find the PO qty for MRP share items
    VAR netQtybeforeMRP = qtyonHand - qtyNeeded
    VAR packsrequired =
        ROUNDUP ( DIVIDE ( netQtybeforeMRP, MPQ, BLANK () ), 0 )
    VAR MRPorderQty =
        IF ( netQtybeforeMRP < 0, -1 * ( packsrequired * MPQ ), BLANK () ) 
    -------------------------------------------------------
    RETURN
        SWITCH (
            TRUE (),
            rlvntitem IN MRPitems, qtyonHand + MRPorderQty - qtyNeeded,
            qtyonHand + nonmrpOpenOrders - qtyNeeded
        )
    


    PFA the pbix file also for your reference.



    ------------------------------
    Gopa Kumar S
    ------------------------------

    Power Summit (bi) - Post


  • 7.  RE: Problem to forecast inventory levels with EDI orders

    Posted Apr 16, 2019 11:36 AM
    @ Gopa Kumar S
    This is super and I really appreciate your help !
    I will test the solution with my live files and will come back with the results when back from travelling next week.
    Thank you so much.
    Regards


    ------------------------------
    Jacques Bruneau
    FINANCE AND I.T. DIRECTOR
    SHERBROOKE QC
    8195732331
    ------------------------------

    Power Summit (bi) - Post


  • 8.  RE: Problem to forecast inventory levels with EDI orders

    Posted Apr 30, 2019 04:53 PM
    Gopa
    I have tested the solution and it works just great. It helped me to understand a lot of things in Dax ! Thanks.
    If I push my luck a bit further, what would be the approach to show the detailed movements in Qty from start inventory to month-end closing quantity in order to validate the figures?

    Thanks again for the super help !
    Regards,

    ------------------------------
    Jacques Bruneau
    FINANCE AND I.T. DIRECTOR
    SHERBROOKE QC
    8195732331
    ------------------------------

    Power Summit (bi) - Post


  • 9.  RE: Problem to forecast inventory levels with EDI orders

    Posted May 12, 2019 06:33 AM
      |   view attached
    Hi @Jacques Bruneau

    Please see if the below helps you. Has created a custom Table with all the entries (opening inventory, PO qty, EDI qty, Qty Needed) on a daily basis. Then a measure is created to get the daily closing value. It is very similar to a stock ledger. The end of month closing value here matches with the earlier solutions measure. So, this basically shows you how that balance was arrived at.


    PFA the pbix file also for your reference.

    ------------------------------
    Gopa Kumar S
    ------------------------------

    Power Summit (bi) - Post


  • 10.  RE: Problem to forecast inventory levels with EDI orders

    Posted May 13, 2019 01:31 PM
      |   view attached
    Hi @Jacques Bruneau

    Just an updated pbix file with the elimination of rows in the table visual when there are no transactions. Making the table visual more manageable. ​

    ------------------------------
    Gopa Kumar S
    ------------------------------

    Power Summit (bi) - Post