Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi team,
I am trying to create weekly sales forecast using:
1. Historical value for the selected week as a reference. I should be able to change the historical week to change the reference.
2. Apply seasonality factor on the forecast.
For example, if the reference week is week 40 and sale was 500 units in that week, then I shall be able to forecast sales value for future weeks (week 52 of 2018 and onwards) by multiplying weekly seasonality factor to 500.
I am struggling with the first part. I think the second part will be easy if the former is fixed.
Any guidance is highly appreciated.
Best regards,
Kamal
Solved! Go to Solution.
Hi Maggie,
Thank you so much for creating the test. I was able to solve the problem by creating two unrelated tables. One as copy for sales table and other for seasonality factors. Then I used slicer of weeks column of sales table to get the average sale for selected weeks. As these tables were not related, the slicer did not affect the forecast table visual.
My next struggle is with inventory forecast, for which I will upload a question shortly. 🙂
Best regards,
Kamal
Per your requirement, i make a test to achieve this.
if there is any difference between my test and your scenario, please let me know.
Dataset:
"historical data" table, "calendar" table, "factor" table,
create two new tables
"slicer_weeknum", "slicer_year"
Create measures in "historical data" table
weekly_value = CALCULATE ( SUM ( 'historical data'[value] ), FILTER ( ALL ( 'historical data' ), 'historical data'[weeknum] = MAX ( 'historical data'[weeknum] ) ) ) selected_year = SELECTEDVALUE(slicer_year[slicer_year]) selected_week = SELECTEDVALUE(slicer_weeknum[slicer_weeknum]) selected_week_value = CALCULATE ( [weekly_value], FILTER ( ALL ( 'historical data' ), 'historical data'[year] = [selected_year] && 'historical data'[weeknum] = [selected_week] ) )
Create calculated columns in "Calendar" table
Calendar_Year = YEAR('calendar'[Date]) Calendar_week = WEEKNUM('calendar'[Date],2) merge1 = CONCATENATE([Calendar_Year],[Calendar_week])
create calcualted column in "factor" table
merge = CONCATENATE(factor[year],factor[week])
create relationship between "Calendar" table and "factor" table based on "merge1" and "merge" columns as the first screenshot
finally, create measures in "Calendar" table
seasonality_factor = LOOKUPVALUE ( factor[seasonality factor], factor[year], MAX ( 'calendar'[Calendar_Year] ), factor[week], MAX ( 'calendar'[Calendar_week] ) ) his+for = VAR historical_data = CALCULATE ( SUM ( 'historical data'[value] ), FILTER ( ALL ( 'calendar' ), 'calendar'[Calendar_week] = MAX ( 'calendar'[Calendar_week] ) ) ) RETURN IF ( historical_data = BLANK (), [seasonality_factor] * [selected_week_value], historical_data )
Best Regards
Maggie
Hi Maggie,
Thank you so much for creating the test. I was able to solve the problem by creating two unrelated tables. One as copy for sales table and other for seasonality factors. Then I used slicer of weeks column of sales table to get the average sale for selected weeks. As these tables were not related, the slicer did not affect the forecast table visual.
My next struggle is with inventory forecast, for which I will upload a question shortly. 🙂
Best regards,
Kamal
Hi Maggie,
Thank you so much for creating the test. I was able to solve the problem by creating two unrelated tables. One as copy for sales table and other for seasonality factors. Then I used slicer of weeks column of sales table to get the average sale for selected weeks. As these tables were not related, the slicer did not affect the forecast table visual.
My next struggle is with inventory forecast, for which I will upload a question shortly. 🙂
Best regards,
Kamal
Perhaps take a look at this and related articles:
Hi Greg, thank you for sharing. I was able to solve the problem by creating a seasonality factors table and a copy of the sales table. I did not create any relationship between these tables so the slicer of sales table (to calculate average sales over selected weeks) did not affect the other forecast table visual.
Regards,
Kamal
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |