cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBIGuy Member
Member

Re: 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/

Business Intelligence Architect / Consultant

Re: How to do a Forecast?

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.

 

http://fountainanalytics.com/creating-simple-time-series-forecasting-using-microsoft-powerbi-and-dax...

 

Regards,

  David

Bustardo Regular Visitor
Regular Visitor

Re: How to do a Forecast?

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.

ss1.jpg

 

After I click on any filter the Values disappear for the Trend Line when using the line and stacked column chart.

 

ss2.jpg

I used “see data” on the first visual, and the Data is there for Claims (trend 2)

ss3.png

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?

ss4.png

Deepikasubu Frequent Visitor
Frequent Visitor

Re: How to do a Forecast?

Hi David the link doesnt have any attachment. Please help in getting those. I am so interested in viewing the post.

djohnson Regular Visitor
Regular Visitor

Re: How to do a Forecast?

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

djohnson Regular Visitor
Regular Visitor

Re: How to do a Forecast?

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

Highlighted
SGLake Frequent Visitor
Frequent Visitor

Re: How to do a Forecast?

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])

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 285 members 3,167 guests
Please welcome our newest community members: