I know this could be done in the SQL code and brought in with my table, but hoping it can be dne in beast mode as hat would allow for greater flexibility.
I have a table - (sample below) - Assume it is April 15th - end of the day (50%) of the way thoguh month.
DateOne, Plan and Actual all are pulled in from an SQL query. Forecast1 is a Beast Mode Calculation that looks at the days of the current paste and adjusts the actual to a straightline based on number of days in month. In the example below we are 50% of the way through the month so the number doubles.
Forecast2 is the one I cannot figure out. I would like to take either the Actual from the current month or Forcast1 from current monthas a starting point to do a clcultion in later months. In the table below, for example, it takes the April Actual of 15 and multiplies by 1. I can get the Beast mode to identify the month correctly and i can hard code a 15 into the outer months, but i cannot figure out how to pull the 15 from April actual to use it in other row calculations. In short, i can do the whole table below other than than the numbers in RED based on a calculation. Can it be done in Beast Mode?
DateOne | Plan | Actual | Forecast1 | Forecast2 |
1/1/2015 | 20 | 15 | | |
2/1/2015 | 20 | 18 | | |
3/1/2015 | 20 | 25 | | |
4/1/2015 | 22 | 15 | 30 | |
5/1/2015 | 22 | | | 15 |
6/1/2015 | 22 | | | 15 |
7/1/2015 | 25 | | | 15 |
8/1/2015 | 25 | | | 15 |
9/1/2015 | 25 | | | 15 |
10/1/2015 | 25 | | | 15 |
11/1/2015 | 27 | | | 15 |
12/1/2015 | 27 | | | 15 |