Forum Discussion
Create a weekly forecast chart
I want to create a weekly forecast chart on sales for the next 2 weeks. I have a daily date field(this contains multiple records for each date) and sales. How do i plot this such that it displays start of week on the x-axis for past and upcoming weeks? Would i need to create possibly a new measure or column from the date field? if yes, how? Thanks
5 Replies
Anonymous - If you do not want to use the built-in forecasting of Power BI line chart (and I understand why because you want the start of the week but can't get that in continuous and categorical you can't use the built-in forecasting). Yes, you could create a forecasting measure. I actually have an example of that where I calculate the slope of the line and then project out. It's kind of complex but can be done. The best example is in my book, DAX Cookbook, Recipe 8 of Chapter 12. Probably more complicated than you need because it takes into account seasonality. But the same basic concept.
You can get the DAX here: https://github.com/gdeckler/DAXCookbook
Thanks for your response. I'm not sure if you made a mistake with the file but looking at the pbix file from your link, this isn't what I'm looking for. It didn't look a forecast but a line graphs with multiple values. what amitchandak suggested gave me close to what i need except that the x-axis isn't showing the actual start of the week date, instead it shows the month year. Thanks
Anonymous - The concept is that you use categorical axis to get your starting week values. Your measure looks forward 2 weeks let's say. Now, you grab the last 2 data points for which you have actual values and you figure out the slope of that line. You use that slope to make your forecast predictions. That is the essense of what the example is showing only much more complicated because it is taking seasonality into account for the sales numbers.
Anonymous , if you want power BI line forecast. You can have the week start date
Week Start Date = [Date] - Weekday([Date]) -1 //Sunday
Week Start Date = [Date] - Weekday([Date],2) -1 //Monday
Plot this in a line visual and forecast for 2 periods use week start date as the axis.
Or You can use R/Python integration
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts
https://en.blog.businessdecision.com/how-to-use-the-python-integrator-in-powerbi/
https://towardsdatascience.com/using-python-in-power-bi-ee95a6b71443
https://powerbi.microsoft.com/en-us/blog/python-visualizations-in-power-bi-service/
https://powerbi.microsoft.com/en-us/blog/python-visualizations-in-power-bi-service/
https://www.ahaapps.com/microsoft-power-bi-service-with-python-visualizations/Thanks for your response. Your solution got me close to what I'm looking for. The only thing about the visualization is that it failed to show the actual start of week on the x-axis when i made the forecast instead it shows the month year. Is there a way i can solve this? Please see below. Thanks.
