Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WESTi
Helper I
Helper I

How to do a forecast without loading forecast data? DAX measure or potentially R?

I have revenue data by month up until a particular data. I would like to show forecast information for dates beyond this point.

 

I have created a separate dates table and have months going forward 3 years and am now trying to figure out how to automate forecast information. Is this possible in PowerBi? 

 

I have been following this article: http://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/ from this post: http://community.powerbi.com/t5/Desktop/How-to-do-a-Forecast/m-p/9249#M1733 but haven't been able to make it work as I cant get the measures to reference the last date value unless I use ALL on the dates table but then it doesn't work on a monthly basis.

 

Basically, I would like to just be able to show an ongoing trend with a monthly growth rate without creating the data as preferably I would like to achieve this via a Direct Query connection to our accounting database (we have an annual budget, but I would like to show projected growth from any month with actuals information).

 

All of my data models and reports end up on the PowerBi service, so I haven't looked at R as it wasn't supported on the service - I believe this has now changed but I am not sure if it would still suffice? Can I create forecast data from my loaded data using R? - I have not used R before, but I am a quick learner and will look into this if it would resolve my issue.

3 REPLIES 3
Greg_Deckler
Super User
Super User

So, for your forecast, is what you want to show is the projected numbers for the next few months (each month) based solely upon the trend over the last few months?

 

There may be some techniques in my article on forecasting that you can use, although if the above is what you want, it will not apply 100%. That being said, my general opinion is that using solely internal data to do forecasts is generally not a good approach as the majority of businesses are impacted by external factors to a far greater degree than most generally realize.

 

http://community.powerbi.com/t5/Community-Blog/Correlation-Seasonality-and-Forecasting-with-Power-BI...

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Your article doesn't achieve what I am after - but it was a fantastic read and does have techniques that I may use in the coming week.

 

I agree with your opinion on forecasting based on internal data - but in this case we are trying to show projected growth based on no external impact - solely by the internal numbers (basically show a growth trend) as our proper forecast which sits inside the data set does include the external factors (but this isn't what we would like to show in this case as we would like a rolling forecast whereas the proper forecast is only done @ an annual level).

 

I will keep looking into this. If I find a solution, I will post here.

Having problems to understand your request:

 

"Basically, I would like to just be able to show an ongoing trend with a monthly growth rate without creating the data as preferably I would like to achieve this via a Direct Query connection to our accounting database (we have an annual budget, but I would like to show projected growth from any month with actuals information)."

 

1) Do you want to show a linear trend or also seasonality in it?

2) What do you mean with monthly growth rate? An additional factor that shall be applied on the trend-calculation or shall this simply represent the current trend ("based on the actuals we have so far, our sales have been growing 2% per month on average")

3) What do you mean with "without creating data" - not loading existing data into PowerBI or not creating any numbers in Power BI?

4) Why do you want a Direct Query connection - wouldn't you only want to import figures from closed months?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.