Incremental Subtract

[neonguyen1803]

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

[tamerj1 · Accepted solution]

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

[tamerj1 · Accepted solution]

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

[neonguyen1803]

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

[tamerj1 · Accepted solution]

@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

[neonguyen1803]

Thank you so much