Forum Discussion
Adding output previous week with a fixed "step" output
Hi!
I am working on a forecast model where we need to predict future output based on history.
What I have is a fixed "Step" output (which I calculated based on historical data). Lets say this step equals 150. What I want to create is a measure which calculates forecast output each week by 150 + the previous week forecast output. The tricky thing is that the first week (min week) should be the minimum of the actual output for the given period. I have a calendar connected to the output fact table.
Anyone have any idea how I can get the Forecast output?
Actual output
| Year | Week | Output |
| 2023 | 50 | 15 |
| 2023 | 51 | 50 |
| 2023 | 52 | 40 |
| 2024 | 1 | 4 |
| 2024 | 2 | 70 |
| 2024 | 3 | 150 |
| 2024 | 4 | 200 |
| 2024 | 5 | 300 |
Forecast output (what I want to achieve)
| Year | Week | Forecast Output |
| 2023 | 50 | 15 |
| 2023 | 51 | 165 (150+15) |
| 2023 | 52 | 315 (150+165) |
| 2024 | 1 | 465 (150+315) |
| 2024 | 2 | 615 (150+465) |
| 2024 | 3 | 765 (150+615) |
| 2024 | 4 | 915 (150+765) |
| 2024 | 5 | 1065 (150+915) |
pls see if this is what you want
12 Replies
pls see if this is what you want
Thanks for your suggested solution, its definetly close. Problem is that I have ouput from from two years in my model (from mid 2023 until today). So the "week2" column gives incorrect week numbers with your calculation. Is there any way we can write a calculated week2 column which takes this into consideration?
do you want to show this to using measure or genarating table ?
if using measure
create measuer ,forcast_output = var output = [output] var y = selectedvalue([year]) var w = selectedvalue([week])-1 var a = calculatetable(min([output]),filter('Actual output',[year]=a && [week]=w)) var b = a+150 return bthen create visual table with column, year, week, and our new measure(forecast_output)
