Forum Discussion
Solved
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 ...
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
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 |