Forum Discussion
How to do a Forecast?
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/
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
David,
Do you think there could be an issue with your prior day formula? It's referring to the site_data[date], -1 but the others like prior week are looking at the CSV_datelookup[date].
Pageviews (Prior Day) =
SUMX(
CALCULATETABLE(Site_Data,
DATEADD(ALL(Site_Data[Date]),-1,DAY),
ALL(Site_Data[Date])),
Site_Data[Pageviews])Pageviews (Prior Week) =
SUMX(
CALCULATETABLE(Site_Data,
DATEADD(CSV_Datelookup[Date],-7,DAY),
ALL(Site_Data[Date])),
Site_Data[Pageviews])
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
