Forum Discussion

neonguyen1803's avatar
neonguyen1803
Frequent Visitor
3 years ago
Solved

Incremental Subtract

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

        Thank you so much