Using MIN won't work because the minimum may not be negative. Also, applying MIN to the Inventory line item will return the value of the inventory, not the date.
Here's how I'd do it: Add a line item called Shortage Date (date format) to the Parts Inventory module, with the formula
IF Inventory < 0 THEN START() ELSE BLANK
Give this line item the same dimensionality as Inventory, including time, and set its summary function to None, Time: First non-blank. (The most important thing is for the sum over time to be first non-blank; it doesn't really matter about the other sum.)
Now, in your First Shortage Date module, you can have a line item Shortage Date (also date format), which doesn't apply to time, with the formula
Parts Inventory.Shortage Date[SELECT: Time.All Periods]
This should give you the first date with a negative inventory, for each item in the Parts list, which I think is what you want.
Thank you for your solution.
Yes! your solution is what i want to realize.
But there is a problem. i'm using weeks : general in calendar settings.
in this case, All period syntax in [SELECT: TIME.All Periods] can not be used.....
Would you tell me more detail about Start Date?
Parts Inventory.Shortage Date
IF Inventory < 0 THEN START() ELSE NEXT(Shortage Date)
I did this.
and this modified formula for First Shortage Date.Shortage Date:
Parts Inventory.Shortage Date[LOOKUP: Start Date]
I added 'Start Date' line item in First Shotage Date Module with date format (this line item time scale is 'day')
and i set shortage date line item formula using lookup functoin you menthioned .(this line item time scale is 'not applicable')
Formula could be set but the result is Blank
Is the any required settings? or do i understand wrong?
I need your more help.
(i attached my line item setting and module download data xls)
It works because it's a recursive formula: defining Shortage Date in terms of NEXT(Shortage Date) makes it work backwards, noting each time it gets a negative Inventory. Each time it notes a date, the date gets earlier & earlier, so that at the end of the process (i.e. the start of the timescale) it's got the earliest date with a negative inventory. Step into it with F8 and you'll see what I mean.
Glad I could help!