Help getting value of a measure based on category index

[merryseeker]

I have three measures AveOnHand (beg inventory for batch), Needs (cumulative demand by date) and Balance ([AvgOnHand]-[Needs]).  I am working with dated batches of product. I need to add the final value of Balance from the oldest batch (index 4) to the beginning inventory of the next batch (index 5.)  In the following example, I need to add -750 to 1227. 

How might I accomplish this? I've spent lots of time searching around and trying various Dax solutions, but I can't get the desired result.

Thanks for any help!

[Greg_Deckler]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

Second, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Third, it is probably something like:

Column =
  VAR __Batch = [Index]
  VAR __PreviousBatch = [Index] - 1
  VAR __OldestPreviousDate = MAXX(FILTER('Table',[Index] = __PreviousBatch),[FutureProdDate])
  VAR __PreviousBalance = MAXX(FILTER('Table',[Index] = __PreviousBatch && [FutureProdDate] = __OldestPreviousDate,[Balance])
  VAR __OldestCurrentDate = MAXX(FILTER('Table',[Index] = __Batch),[FutureProdDate])
RETURN
  IF('Table'[FutureProdDate] = __OldestCurrentDate,[Balance] + __PreviousBalance,[Balance])