Forum Discussion

neonguyen1803's avatar
neonguyen1803
Frequent Visitor
3 years ago
Solved

Incremental Subtract

Dear all, I have a table below: 

 

DateCusIDProIDStock
13/10/20231110
13/10/2023125
13/10/2023137
13/10/20232113
13/10/2023228
13/10/20232315
19/10/20231115
19/10/2023127
19/10/2023139
19/10/20232110
19/10/20232210
19/10/2023239
24/10/20231120
24/10/20231215
24/10/20231315
24/10/2023215
24/10/2023223
24/10/2023233
31/10/20231130
31/10/20231230
31/10/20231330
31/10/20232120
31/10/20232225
31/10/20232325

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

Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
    tamerj1
    3 years ago

    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

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      neonguyen1803
      Frequent Visitor
      3 years ago

      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's avatar
        tamerj1
        Community Champion
        3 years ago

        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