Forum Discussion

9mikejacobs's avatar
9mikejacobs
Frequent Visitor
5 years ago
Solved

How to utilize previous lines record for next lines computation

Hello, 

 

I have a data model that contains the below tables.

 

Inventory - snapshot of current inventory position as of today

PO Table - Open PO's awaiting delivery

Open Deliveries - Open deliveries waiting to be picked and shipped

Open Orders - Open sales orders that will be picked and shipped in the future

 

These tables are all linked to the inventory table and a date table.  What I am trying to do is take the current inventory postion for todays date (shows in week 40 below) and add the PO amount to that qty and subtract the deliveries and sales order quatities.  The Inventory Rolling measure does just that.  However I need to then utilize the result from today as the Inventory position for tomorrow and so on down the line. 

 

Week 40 shows inventory ending at -305,468.  I need that to be the starting inventory position for week 41 and then I would use that figure and add in week 41 PO qtys and subtract week 41 deliveries and order qty's.  Any ideas how I can best utilize the previous days inventory record for the current inventory level?

 

 

Thanks, 

 

Mike

 

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, 9mikejacobs 

    Here are the steps you can follow to achieve

    1. sort the YearWeek column
    2. create an index column in the power query editor, you can follow this picture:

     

    3.create measure for Inventory Rolling

    Inventory Rolling = var _index=MAX('Table'[Index])

var _inv=CALCULATE(sum('Table'[ Inventory ]),FILTER(ALL('Table'),'Table'[Index]<=_index))

var _poq=CALCULATE(sum('Table'[ PO Order Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))

var _ooq=CALCULATE(sum('Table'[ Open Order Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))

var _odq=CALCULATE(sum('Table'[ Open Deliveries Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))

return _inv+_poq-_ooq-_odq

    4.create measure for inventory:

    Inventory = var _inv=CALCULATE([Inventory Rolling],FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))

return if(ISBLANK(_inv),CALCULATE(MAX('Table'[ Inventory ]),'Table'[Index]=1),_inv)

    And you can get your expected chart like this:

     

     

    Best Regards,

    Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    9mikejacobs Sorry, having trouble following, can you post sample data as text and expected output?


    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • 9mikejacobs's avatar
      9mikejacobs
      Frequent Visitor
      5 years ago

       

      Row one computes correctly as my raw data has the inventory value of 126,600.  So at end of week 40 I'm left with inventory of -254,200 which you see in the "inventory rolling" column.  Now I need that value to drop in the inventory value for row 2 and then the computation occurs on that row and inventory at end of week 41 is -114,200.  I need that to continue down the table. 

       

      My dilemma is how can I store/retrive that result and use it as my inventory value for the next record.

       

      row 1 - 126,000 + PO Order Qty - Open Deliveries Qty - Open Order Qty 

      row 2 - -254,200 + PO Order Qty - Open Deliveries Qty - Open Order Qty 

       

      Hopefully that makes more sense!

       

      What I want to happen:

      YearWeekMaterial Plant Key Inventory  PO Order Qty  Open Deliveries Qty  Open Order Qty  Inventory Rolling  My Inventory value at end of week 
      2020-402023-4102        126,600                  380,800                     (254,200)
      2020-412023-4102      (254,200)          140,000                      (114,200)
      2020-422023-4102      (114,200)          168,000                         53,800
      2020-432023-4102          53,800          120,000                             4,500                      169,300
      2020-442023-4102        169,300          220,000                         389,300
      2020-452023-4102        389,300                                -       389,300
      2020-462023-4102        389,300                    90,000                        299,300
      2020-472023-4102        299,300          156,000                    156,000     455,300
      2020-482023-4102        455,300          264,000                    264,000     719,300
      2020-492023-4102        719,300                            80,000                    (80,000)     639,300
      2020-502023-4102        639,300          120,000                    120,000     759,300
      2020-512023-4102        759,300          300,000                        600,000                  (300,000)     459,300
      2020-522023-4102        459,300                                -       459,300
      2021-012023-4102        459,300                                -       459,300
      2021-022023-4102        459,300          372,000                 850,000                 (478,000)      (18,700)
      2021-032023-4102        (18,700)                                -        (18,700)
      2021-042023-4102        (18,700)          144,000                    144,000     125,300

       

       

      What is happening:

      YearWeekMaterial Plant Key Inventory  PO Order Qty  Open Deliveries Qty  Open Order Qty  Inventory Rolling  
      2020-402023-4102        126,600                  380,800                 (254,200) 
      2020-412023-4102           140,000                    140,000 
      2020-422023-4102           168,000                    168,000 
      2020-432023-4102           120,000                             4,500                   115,500 
      2020-442023-4102           220,000                    220,000 
      2020-452023-4102                                 -   
      2020-462023-4102                     90,000                   (90,000) 
      2020-472023-4102           156,000                    156,000 
      2020-482023-4102           264,000                    264,000 
      2020-492023-4102                             80,000                    (80,000) 
      2020-502023-4102           120,000                    120,000 
      2020-512023-4102           300,000                        600,000                  (300,000) 
      2020-522023-4102                                 -   
      2021-012023-4102                                 -   
      2021-022023-4102           372,000                 850,000                 (478,000) 
      2021-032023-4102                                 -   
      2021-042023-4102           144,000                    144,000 
  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, 9mikejacobs 

    Here are the steps you can follow to achieve

    1. sort the YearWeek column
    2. create an index column in the power query editor, you can follow this picture:

     

    3.create measure for Inventory Rolling

    Inventory Rolling = var _index=MAX('Table'[Index])

var _inv=CALCULATE(sum('Table'[ Inventory ]),FILTER(ALL('Table'),'Table'[Index]<=_index))

var _poq=CALCULATE(sum('Table'[ PO Order Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))

var _ooq=CALCULATE(sum('Table'[ Open Order Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))

var _odq=CALCULATE(sum('Table'[ Open Deliveries Qty ]),FILTER(ALL('Table'),'Table'[Index]<=_index))

return _inv+_poq-_ooq-_odq

    4.create measure for inventory:

    Inventory = var _inv=CALCULATE([Inventory Rolling],FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))

return if(ISBLANK(_inv),CALCULATE(MAX('Table'[ Inventory ]),'Table'[Index]=1),_inv)

    And you can get your expected chart like this:

     

     

    Best Regards,

    Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.