Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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) |
Solved! Go to Solution.
pls see if this is what you want
Proud to be a Super User!
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)
@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.
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?
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?
Proud to be a Super User!
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!
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
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.
you are welcome
Proud to be a Super User!
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?
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |