Forecast with irregular dates

[Anonymous]

I have some stock market data for a number of companies. I'm trying to demonstrate a simple forecast function using said data, but Power BI is giving me an error message that reads "Data is too irregular to Forecast". 

When I go to more information I get:

"Your data isn't evenly spaced

Forecast can't be created because the timeline isn't evenly spaced. The dates or numbers in a valid timeline must have a consistent gap between consecutive points." 

Any ideas on how I might fix this? I have several years worth of data and line chart itself looks just fine. As with most stock market data there is going to be major ups and downs, but in this particular dataset there is still a general upward trend. I've tried to create a second column for an index (thinking it could be some issues with the dates not including holidays and weekends), but that hasn't turned up a valid solution. 

Any help / thoughts would be great. 

[Anonymous · Accepted solution]

@Anonymous,

Firstly, create a date table that contains all possible and continuous dates, then create relationship between the date table and your original table.

Secondly, merge the date table and your original table based on the date field into a new query.

Thirdly, create line chart using date key of merged table as X-Axis of Line chart, then you will be able to add forecast line. For more details about solving the above error, please review the following blog.

http://www.erikhudzik.com/2017/01/16/power-bi-forecasting-feature-and-when-your-data-is-too-irregular-to-forecast/


Regards,
Lydia

[Yorick_DR]

Thanks! Works for me. Great explanation via the url.

[TylerVono]

Hello Lydia, Can you show me where to merge the date table and original table based on the date field into a new query?

[Anonymous]

Hi @Anonymous,

Thanks for the detailed information and the URL for the example. It looks like that will meet my needs pretty well. 

Thanks again!

Jonathan