Adding output previous week with a fixed "step" output

[mrhish]

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)

[Fendiaz]

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 b

then create visual table with column, year, week, and our new measure(forecast_output)

[mrhish]

@ryan_mayu I do have a date table connected to the fact table where the actual ouput is. So its a simple model with a date table connected to the fact table. 

[ryan_mayu]

pls see if this is what you want

[mrhish]

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? 

[ryan_mayu]

then when will the week start? Will we recalculate the week in the next year? could you pls update the sample data and provide the exepcted output?

[mrhish]

I updated my first post with expected ouput. Just to clarify - the start date is just an example, in my "real" model it starts in the middle of 2023. But just wantad to give you some sample data. Forecast should not be recalculated when a new year starts. Many thanks for your help!

[ryan_mayu]

pls see the attachment below

[mrhish]

Sorry, but just cant get it right. I attached a sample excel file where Column Q is the expected outcome. Most important is to calculate trend number in Column I. Everyhing which looks easy in Excel is sometimes a bit trickier in dax :/.

https://1drv.ms/x/s!Aq-WL4O1vixn3Dv-gw7mWdFzLKQO?e=Bv3Q60 

[ryan_mayu · Accepted solution]

pls see if this is what you want

[mrhish]

Awsome, thanks! I created a index column ranked on year and week and calculated the trend on that column instead of your week2. Your week2 didnt work as its based on Concat which is text format and thus weeks gets wrongly sorted (e.g. week 1 and 10 happens after each other). But now its all sorted. Thanks again for your help.

[ryan_mayu]

you are welcome

[ryan_mayu]

@mrhish 

do you have the date table in your model? i think the actual output is a table visual. could you pls provide some sample data?