Create a weekly forecast chart

[Anonymous]

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

[amitchandak]

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

https://powerbi.microsoft.com/pt-br/blog/introducing-new-forecasting-capabilities-in-power-view-for-office-365/

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/

[Anonymous]

@amitchandak 

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.

[Greg_Deckler]

@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

[Anonymous]

@Greg_Deckler 

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

[Greg_Deckler]

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