Cumulative subraction in Column

[Giancarlo1977]

Hi,

I am looking for a formula that should give me the result as per table below in last column "Cumulative remaining stock"

Country	serial number	Item Number	Concatenate	QTY Item Number in stock	Qty Item Required for each serial number	Cumulative remining stock
US	111	a	US_a	2	1	1
US	222	a	US_a	2	1	0
US	333	a	US_a	2	1	-1
US	444	a	US_a	2	1	-2
Canada	555	a	Canada_a	3	1	2
Canada	666	a	Canada_a	3	1	1
Canada	777	a	Canada_a	3	1	0
Canada	888	a	Canada_a	3	1	-1

I have different serial number that are using the same item number. So I want to calculate the remaining stock considering that all of them are using the same item. In addition I should consider a filter for the country.

Can you please help?

[Giancarlo1977]

Thanks a lot @barritown,

it is working perfectly for me!

Regards

Giancarlo

[barritown · Accepted solution]

Hi @Giancarlo1977,

You could try such a calculated a column:

Here's the code in plain text for convenience:

Cumulative remaining stock = 
VAR CurrentSelection = [Concatenate]
VAR CurrentSN = [serial number]
VAR Delta = SUMX ( FILTER ( 'Table', [Concatenate] = CurrentSelection && [serial number] <= CurrentSN ), [Qty Item Required for each serial number] )
RETURN [QTY Item Number in stock] - Delta

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian