Subtract values based off date

[MrJayDee]

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

[Irwan · Accepted solution]

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.