topic Re: Incremental Subtract in DAX Commands and Tips

Incremental Subtract

neonguyen1803 — Thu, 15 Jun 2023 17:17:13 GMT

Dear all, I have a table below:

Date	CusID	ProID	Stock
13/10/2023	1	1	10
13/10/2023	1	2	5
13/10/2023	1	3	7
13/10/2023	2	1	13
13/10/2023	2	2	8
13/10/2023	2	3	15
19/10/2023	1	1	15
19/10/2023	1	2	7
19/10/2023	1	3	9
19/10/2023	2	1	10
19/10/2023	2	2	10
19/10/2023	2	3	9
24/10/2023	1	1	20
24/10/2023	1	2	15
24/10/2023	1	3	15
24/10/2023	2	1	5
24/10/2023	2	2	3
24/10/2023	2	3	3
31/10/2023	1	1	30
31/10/2023	1	2	30
31/10/2023	1	3	30
31/10/2023	2	1	20
31/10/2023	2	2	25
31/10/2023	2	3	25

Then I want to calculate remain stock by time, Customer and Product like below:

It means from 19/10/2023, with customer 1 and product 1, the remaining stock will calculated by stock on 19/10/2023 - stock on 13/10/2023 --> results is 5

Please help me explain for this case

Re: Incremental Subtract

tamerj1 — Thu, 15 Jun 2023 18:13:28 GMT

Hi neonguyen1803
Please refer to attached sample file with proposed calculated column solution

Remain Stock = VAR CurrentStock = 'Table'[Stock] VAR Change = SUMX ( FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CusID], 'Table'[ProID] ) ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) ), 'Table'[Stock] ) RETURN CurrentStock - Change

Re: Incremental Subtract

neonguyen1803 — Fri, 16 Jun 2023 01:45:17 GMT

It's really great. One more question, I just want subtract with nearlest day, example: 19/10/2023 will subtract 13/10/2023, 24/10/2023 subtract 19/10/2023. Please help me explain for extended question

Re: Incremental Subtract

tamerj1 — Fri, 16 Jun 2023 05:38:54 GMT

neonguyen1803
Yes sure, here you go

Remain Stock = VAR CurrentStock = 'Table'[Stock] VAR PreviousStock = SUMX ( TOPN ( 1, FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CusID], 'Table'[ProID] ) ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) ), 'Table'[Date] ), 'Table'[Stock] ) RETURN CurrentStock - PreviousStock

Re: Incremental Subtract

neonguyen1803 — Fri, 16 Jun 2023 08:43:52 GMT

Thank you so much