Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
mrhish
Frequent Visitor

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

YearWeekOutput
20235015
20235150
20235240
202414
2024270
20243150
20244200
20245300

 

Forecast output (what I want to achieve)

YearWeekForecast Output
20235015
202351165 (150+15)
202352315 (150+165)
20241465 (150+315)
20242615 (150+465)
20243765 (150+615)
20244915 (150+765)
202451065 (150+915)
1 ACCEPTED SOLUTION

pls see if this is what you want





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

12 REPLIES 12
Fendiaz
New Member

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
Frequent Visitor

@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. 

pls see if this is what you want

11.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?





Did I answer your question? Mark my post as a solution!

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!

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 

pls see if this is what you want





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.