Forum Discussion
Inventory running total
I am trying to replicate the ERP stock transactional history table and then want to visualize it in a graph.
So we have the current stock level and the inventory transaction history.
I would like to show a graph of the inventory level therefore want to calculate the inventory running total.
The current stock level is 17 therefore the first lines running total = 17
Running Total = Previous lines Running Total - Previous lines TotalQty
| PartNum | TranDate | TranNum | TranType | OnhandQty | Qty | TotalQty | Running Total |
| Part 0001 | 01/06/2018 | 10716074 | STK-MTL | 17 | -1 | -1 | 17 |
| Part 0001 | 01/06/2018 | 10716075 | STK-MTL | 17 | -1 | -1 | 18 |
| Part 0001 | 01/06/2018 | 10716076 | STK-MTL | 17 | -1 | -1 | 19 |
| Part 0001 | 01/06/2018 | 10716077 | STK-MTL | 17 | -1 | -1 | 20 |
| Part 0001 | 01/06/2018 | 10716078 | STK-MTL | 17 | -1 | -1 | 21 |
| Part 0001 | 01/06/2018 | 10716079 | STK-MTL | 17 | -1 | -1 | 22 |
| Part 0001 | 01/06/2018 | 10716080 | STK-MTL | 17 | -1 | -1 | 23 |
| Part 0001 | 31/05/2018 | 10716071 | PUR-STK | 17 | 25 | 25 | 24 |
| Part 0001 | 30/05/2018 | 10713112 | STK-CUS | 17 | -1 | -1 | -1 |
| Part 0001 | 23/05/2018 | 10703893 | ADJ-QTY | 17 | 3 | 3 | 0 |
| Part 0001 | 23/05/2018 | 10703894 | STK-MTL | 17 | -1 | -1 | -3 |
| Part 0001 | 23/05/2018 | 10703896 | STK-MTL | 17 | -1 | -1 | -2 |
| Part 0001 | 23/05/2018 | 10703898 | STK-MTL | 17 | -1 | -1 | -1 |
| Part 0001 | 23/05/2018 | 10703899 | STK-MTL | 17 | -1 | -1 | 0 |
| Part 0001 | 15/05/2018 | 10497083 | STK-MTL | 17 | -1 | -1 | 1 |
| Part 0001 | 14/05/2018 | 10496626 | STK-MTL | 17 | -1 | -1 | 2 |
| Part 0001 | 08/05/2018 | 10484078 | STK-MTL | 17 | -1 | -1 | 3 |
| Part 0001 | 08/05/2018 | 10484454 | STK-MTL | 17 | -1 | -1 | 4 |
| Part 0001 | 08/05/2018 | 10485071 | STK-MTL | 17 | -1 | -1 | 5 |
| Part 0001 | 08/05/2018 | 10485441 | STK-MTL | 17 | -1 | -1 | 6 |
| Part 0001 | 08/05/2018 | 10486266 | STK-MTL | 17 | -1 | -1 | 7 |
| Part 0001 | 08/05/2018 | 10486603 | STK-MTL | 17 | -1 | -1 | 8 |
Hi Sharksguts
Create an index column from 0 in query editor.
Then create two calculated columns
Column1 = CALCULATE(SUM(Sheet1[TotalQty]),FILTER(ALL(Sheet1),[Index]<EARLIER([Index])))
Column2 = [OnhandQty]-[Column1]
Best Regards
Maggie
3 Replies
Hi Sharksguts,
How is your table sorted?
From the data below I can't see anything obvious and I think we need to know this to help with this formula.
Thanks
Alex
Good point,
The table would be sorted PartNum(Ascending), TranDate(Descending), SysTime(Descending)
The Running Total would be reset on the PartNum changing.
PartNum TranDate SysTime TranType OnhandQty Qty TotalQty Running Total PART-0001 01/06/2018 43202 STK-MTL 17 -1 -1 17 PART-0001 01/06/2018 43230 STK-MTL 17 -1 -1 18 PART-0001 01/06/2018 43259 STK-MTL 17 -1 -1 19 PART-0001 01/06/2018 43288 STK-MTL 17 -1 -1 20 PART-0001 01/06/2018 43316 STK-MTL 17 -1 -1 21 PART-0001 01/06/2018 43343 STK-MTL 17 -1 -1 22 PART-0001 01/06/2018 43368 STK-MTL 17 -1 -1 23 PART-0001 31/05/2018 42843 PUR-STK 17 25 25 24 PART-0001 30/05/2018 57220 STK-CUS 17 -1 -1 -1 PART-0001 23/05/2018 37981 ADJ-QTY 17 3 3 0 PART-0001 23/05/2018 38008 STK-MTL 17 -1 -1 -3 PART-0001 23/05/2018 38034 STK-MTL 17 -1 -1 -2 PART-0001 23/05/2018 38058 STK-MTL 17 -1 -1 -1 PART-0001 23/05/2018 38083 STK-MTL 17 -1 -1 0 PART-0001 15/05/2018 29635 STK-MTL 17 -1 -1 1 PART-0001 14/05/2018 58672 STK-MTL 17 -1 -1 2 PART-0001 08/05/2018 49447 STK-MTL 17 -1 -1 3 PART-0001 08/05/2018 50725 STK-MTL 17 -1 -1 4 PART-0001 08/05/2018 52428 STK-MTL 17 -1 -1 5 PART-0001 08/05/2018 53770 STK-MTL 17 -1 -1 6 PART-0001 08/05/2018 55161 STK-MTL 17 -1 -1 7 PART-0001 08/05/2018 57792 STK-MTL 17 -1 -1 8
Hi Sharksguts
Create an index column from 0 in query editor.
Then create two calculated columns
Column1 = CALCULATE(SUM(Sheet1[TotalQty]),FILTER(ALL(Sheet1),[Index]<EARLIER([Index])))
Column2 = [OnhandQty]-[Column1]
Best Regards
Maggie
