Forum Discussion
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 working the way I need it to:
Remaining Inv = 'Working Page'[On Hand]
-MAXX(TOPN(1,FILTER('Working Page','Working Page'[ID]=EARLIER('Working Page'[ID])&&'Working Page'[Ship Date]<EARLIER('Working Page'[Ship Date])),'Working Page'[Ship Date]),'Working Page'[Order Qty])
I need a column that takes the On hand inventory and subtracts the qty ordered based off date. However, the issue is its taking the total On hand rather than the difference from the previous ship date. The results I am looking for are in the Remaining Inv.
| ID | On Hand | Item Name | Description | Order Qty | Location | Ship To | Index | Ship Date | Remaining Inv |
| Item1Location1 | 23139 | Item1 | RB | 100 | Location1 | Customer 1 | 1 | 7/2/2024 | 23039 |
| Item1Location1 | 23139 | Item1 | RB | 100 | Location1 | Customer 2 | 2 | 7/3/2024 | 22939 |
| Item1Location1 | 23139 | Item1 | RB | 100 | Location1 | Customer 3 | 3 | 7/3/2024 | 22839 |
| Item1Location1 | 23139 | Item1 | RB | 200 | Location1 | Customer 4 | 4 | 7/3/2024 | 22639 |
| Item1Location1 | 23139 | Item1 | RB | 100 | Location1 | Customer 5 | 5 | 7/5/2024 | 22539 |
| Item1Location1 | 23139 | Item1 | RB | 300 | Location1 | Customer 6 | 6 | 7/5/2024 | 22239 |
| Item1Location1 | 23139 | Item1 | RB | 200 | Location1 | Customer 7 | 7 | 7/23/2024 | 22039 |
| Item1Location1 | 23139 | Item1 | RB | 1300 | Location1 | Customer 8 | 8 | 7/24/2024 | 20739 |
| Item1Location1 | 23139 | Item1 | RB | 2100 | Location1 | Customer 9 | 9 | 7/24/2024 | 18639 |
| Item1Location1 | 23139 | Item1 | RB | 800 | Location1 | Customer 10 | 10 | 7/24/2024 | 17839 |
| Item1Location1 | 23139 | Item1 | RB | 1400 | Location1 | Customer 11 | 11 | 7/24/2024 | 16439 |
| Item1Location1 | 23139 | Item1 | RB | 2100 | Location1 | Customer 12 | 12 | 7/24/2024 | 14339 |
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]-_PreviousSumThe 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.
1 Reply
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]-_PreviousSumThe 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.