Forum Discussion
How to do a Forecast?
Hi,
I know that the forecast functionnality has been removed from PowerBI https://support.powerbi.com/forums/265200-power-bi/suggestions/8790061-add-forecasting-feature
but do you have a formula (dax) for it? For example it generates an average of the previous months and generates a trend for the next 3 months.
Thanks
Try taking a look at the following links. Hope this helps.
Issue One:http://www.daxpatterns.com/cumulative-total/
Issue Two: http://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/
16 Replies
What you want to do sounds very much like simple linear regression. You should be able to do that in a DAX measure or combination of measures/columns. Can you give me some sample data?
Here is Linear Regression
https://www.easycalculation.com/statistics/learn-regression.php
Here is a DAX pattern for moving average that you might find useful
http://www.daxpatterns.com/statistical-patterns/
thank you Greg_Deckler I am not familiar with linear regression. I have have this sample data
Month Sales January 375,09 February 375,01 March 375 April 374,95 May 374,93 June 374,92 July 374,88 August 374,8 September 374,71 October ? November ? December ? What I want to do : a formula which calculates automatically the sales for the next three months, based on an average of the previous month. if we are in september, it generates a trend for oct/nov/dec based on the average. If we are in december, then jan/feb/march.
Can you help me?
Assuming that "," are "." in my geographic context, I plugged your numbers into my model and came up with:
Data:
X,Y
1,375.09
2,375.01
3,375.00
4,374.95
5,374.93
6,374.92
7,374.88
8,374.80
9,374.71Estimates (from linear regression)
10,374.72
11,374.68
12,374.64
That's linear regression, if you want a rolling average, then you could use the pattern here:
http://www.daxpatterns.com/statistical-patterns/
For rolling average. However, I cannot tell from your information how many months that you want to include in your rolling average, the last 3 months? So September would take into account July, August and September to predict December, June, July and August to predict November and May, June, July to predict October, or do you want all available dates to be used in prediction such that [Start] through July to predict October, [Start] through August to predict November and [Start] through September to predict December? Or something else all together??
See my linear regression post:
http://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9272#M1736
I took the approach of using DAX to grab historical points in a time-series to plot future values (via measures). The trick was in creating a date table that had future dates to serve as the parent to the data table. Although this is a rather simple approach, I was able to get a pretty accurate fit for web traffic that still allowed PowerBI cross-viz drill-functionality and PowerBI service support (wheras R does not). I have the pbix file and the supporting data sets in the zip at the bottom of the post.
Regards,
David
Sorry for the bit of Thread necromancy but I'm having an issue. I used David's solution to attempt to do a forecast and I can't understand the behavior that is occuring with the visual.
I have a count and a Trend count, I also have some filters on the side.
After I click on any filter the Values disappear for the Trend Line when using the line and stacked column chart.
I used “see data” on the first visual, and the Data is there for Claims (trend 2)
I did this on the 2nd Visualization as well, where the Trend Line vanishes. But when I look at the See data there Is data for those dates. Any idea what’s going on?
The one thing you will have to do is have a separate table with the dates - which is a parent to the table with the actuals and the forecast DAX. Otherwise, the DAX formula only looks at previous dates. If you look at the example file in the article, you will see the date table and the joins. Hope this helps.
http://fountainanalytics.com/wp-content/uploads/2016/09/TimeSeriesForecast.zip
D
Hi David the link doesnt have any attachment. Please help in getting those. I am so interested in viewing the post.
Sorry, I noticed my site was down and have corrected this. You should now be able to download the .zip file in the bottom of the article. Here is the direct link, if that is easier : http://fountainanalytics.com/wp-content/uploads/2016/09/TimeSeriesForecast.zip
Regards,
D
