Inventory running total

[Sharksguts]

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

[v-juanli-msft · Accepted solution]

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

[alexei7]

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

[Sharksguts]

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