Forum Discussion

MrJayDee's avatar
MrJayDee
Regular Visitor
2 years ago
Solved

Subtract values based off date

Hello,   I am trying to write a formula that calculates remaining inventory based off a ship date and I am about to rage quit because I cannot figure it out! This is my current formula and its not ...
Need Help
Tips and Tricks
  • Irwan's avatar
    Irwan
    2 years ago

    Hello MrJayDee 

     

    please check if this accomodate your need.

     

    Remaining Inv =
    var _PreviousSum =
    SUMX(
        FILTER(
            'Table',
            'Table'[Index]<EARLIER('Table'[Index])
        ),
        'Table'[Order Qty]
    )
    Return
    'Table'[On Hand]-'Table'[Order Qty]-_PreviousSum
     
    The idea of this result is :
    1. _PreviousSum is used for finding sum value of previous index (that 'Index' column is great on finding previous sum value).
    2. substract 'On Hand' column with 'Order Qty' then substract again previous sum value (since _PreviousSum only calculate previous index, current index is not calculated)
     
    Hope this will help you.
    Thank you.
Irwan's avatar
Irwan
Super User
2 years ago

Hello MrJayDee 

 

please check if this accomodate your need.

 

Remaining Inv =
var _PreviousSum =
SUMX(
    FILTER(
        'Table',
        'Table'[Index]<EARLIER('Table'[Index])
    ),
    'Table'[Order Qty]
)
Return
'Table'[On Hand]-'Table'[Order Qty]-_PreviousSum
 
The idea of this result is :
1. _PreviousSum is used for finding sum value of previous index (that 'Index' column is great on finding previous sum value).
2. substract 'On Hand' column with 'Order Qty' then substract again previous sum value (since _PreviousSum only calculate previous index, current index is not calculated)
 
Hope this will help you.
Thank you.