Subtracting rows

[RadoslawM]

Hi folks,

I'm new here and quite new to power BI.

I have two tables:

1. Items with purchase date & Quantity

ItemNo	PurchaseDate	Quantity	Index
854	20/03/2019	32	2
854	30/04/2019	480	3
854	07/05/2019	16	4
854	27/01/2019	203	1
346	20/05/2019	3	2
346	23/07/2019	2	3
346	22/01/2019	1	1
346	08/08/2019	1	4

2. Sales forecast

ItemNo	Forecast
854	350
346	2

I would like to add Balance column to my table 1, that will subtract forecast from quantity, starting with the oldest PurchaseDate. My desired outcome is this:

ItemNo	PurchaseDate	Quantity	Index	Balance
854	20/03/2019	32	2	0
854	30/04/2019	480	3	365
854	07/05/2019	16	4	16
854	27/01/2019	203	1	0
346	20/05/2019	3	2	2
346	23/07/2019	2	3	2
346	22/01/2019	1	1	0
346	08/08/2019	1	4	1

How can I achieve this with simple measures?

Thanks in advance.

[v-juanli-msft]

Hi @RadoslawM 

My output is a bit different from yours.

Create columns

clc_quan = CALCULATE(SUM('Table 1'[Quantity]),FILTER(ALLEXCEPT('Table 1','Table 1'[ItemNo]),'Table 1'[PurchaseDate]<=EARLIER('Table 1'[PurchaseDate])))

clc_quan = CALCULATE(SUM('Table 1'[Quantity]),FILTER(ALLEXCEPT('Table 1','Table 1'[ItemNo]),'Table 1'[PurchaseDate]<=EARLIER('Table 1'[PurchaseDate])))

Column = IF([clc_for]=BLANK(),0,[clc_quan]-[clc_for])

Please tell me if i misunderstand anything.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

How have you arrived at the numbers in the Balance column?  What is the logic?

[RadoslawM]

SUM of Quantity minus Forecast = Balance is my logic, but it would only work if the table was grouped by item.