Calculating based on a previous value when initial column values differ from the rest

[former_member506017]

Let me preface this by saying I am new to the BO world. I'm trying to build a report/chart that tracks incoming loads. The first supply on hand value is pulled from the database and each successive supply on hand is based on the previous row's values.

In the supply arriving column below, the iniital value differs from the rest using the formula:

     =If [Row Number]=1 Then [First Supply Arriving] Else [Supply Arriving]

The supply on hand uses the same idea, but is where I'm running into problems. The first column value should be [Supply On Hand], easy enough. Each successive column value needs to follow this equation:

     Previous([Supply On Hand] (this won't always be [Supply On Hand] though, only for the second row essentially - problem 1) + Previous([Supply Arriving] (same as problem 1, where this is first supple arriving for the second row and then supply arriving moving forward) - [Usage Rate]

If someone could help me in getting the calculation to pull the previous values correctly without making a ton of variables, I'd greatly appreciate it. We just upgraded to 4.1

[Former Member]

Whats Previous([Supply On Hand]+Previous([Supply Arriving] - [Usage Rate]? You have to add both and subtract the usage rate? Whats usage rate then? Also could you provide an example how target looks like?

[former_member506017]

Yes, the supply on hand and the supply arriving from the previous row need added together. The usage rate, which is a constant is then subtracted from that. The result is the on hand total for the next day.

For example:

Date	On Hand	Arriving	Usage Rate
9/9/2014	100,000	250,000	100,000
9/10/2014	250,000	1,000,000	100,000
9/11/2014	1,150,000	0	100,000

[Former Member]

Previous([Supply On Hand] where [Date] = RelativeDate(CurrentDate()-1)

Previous([Supply Arriving] where [Date] = RelativeDate(CurrentDate()-1)

Will give the previous values of both supply and arriving. Add those together and subtract usage rate from it.

[former_member506017]

My problem is that the first supply on hand and arriving are different from the subsequent ones. The first supply arriving is a different calculation; whereas the rest are just sums for each respective day.

So, 9/10 supply on hand needs to be

     Previous([Supply On Hand]) + Previous([First Supply Arriving]) - [Usage Rate]

9/11 needs to be:

     Previous([9/10's calculated supply on hand]) + Previous([Supply Arriving]) - [Usage Rate]

9/12 needs to be:

     Previous([9/11's calculated supply on hand]) + Previous([Supply Arriving]) - [Usage Rate]

I don't know how to get the second row's values.