cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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?

 

9mikejacobs_1-1601320862627.png

 

Thanks, 

 

Mike

 

1 ACCEPTED SOLUTION
Community Support
Community Support

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:

v-robertq-msft_0-1602038626989.png

 

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:

v-robertq-msft_1-1602038626993.png

 

 

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.

View solution in original post

3 REPLIES 3
Community Support
Community Support

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:

v-robertq-msft_0-1602038626989.png

 

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:

v-robertq-msft_1-1602038626993.png

 

 

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.

View solution in original post

Super User IV
Super User IV

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


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




 

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 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors