Create Automatic Forecasts From Historic Data in Power BI using DAX
ฝัง
- เผยแพร่เมื่อ 23 ส.ค. 2024
- A lot of times in business you will make your forecasts and budgets off historic data. In this video I show you how you can do just that using time intelligence functions in Power BI. Really shows you how powerful the DAX language can be where you can retrieve these results so quick and easy
****Video Details****
0:15 - Introduction
1:28 - How To Create Automated Forecast From Historic Data
4:35 - Using Variables To Create One Measure For A 3-Year Forecast
7:15 - Creating The Visualization
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - 60 Page DAX Reference Guide Download - enterprisedna....
FREE - Power BI Resources - enterprisedna.c...
Enterprise DNA Membership - enterprisedna....
Enterprise DNA Online - portal.enterpri...
Enterprise DNA Events - enterprisedna....
**** Related Links ****
Forecasting Analysis Techniques in Power BI with DAX
blog.enterpris...
Explore Forecasting Logic In Your Power BI Models Using DAX
blog.enterpris...
Create Dynamic Cumulative Totals Using DAX In Power BI
blog.enterpris...
**** Related Course Modules ****
Budgeting & Forecasting
portal.enterpri...
Solving Analytical Scenarios w/Power BI & DAX
portal.enterpri...
Time Intelligence Calculations
portal.enterpri...
**** Related Support Forums ****
Cumulative Forecasting
forum.enterpri...
Cumulative Total This Year vs Last
forum.enterpri...
Using Current Trend To Forecast Sales
forum.enterpri...
For more forecasting queries to review see here…..
forum.enterpri...
***** Related Links *****
Forecasting Analysis Techniques in Power BI with DAX
blog.enterprisedna.co/2019/03/13/forecasting-techniques-in-power-bi-by-using-dax/
Explore Forecasting Logic In Your Power BI Models Using DAX
blog.enterprisedna.co/2019/04/08/forecasting-techniques-in-power-bi-using-dax-formula/
Create Dynamic Cumulative Totals Using DAX In Power BI
blog.enterprisedna.co/2019/07/08/create-dynamic-cumulative-totals-using-dax-in-power-bi/
***** Related Course Modules *****
Budgeting & Forecasting
portal.enterprisedna.co/p/budgeting-and-forecasting
Solving Analytical Scenarios w/Power BI & DAX
portal.enterprisedna.co/p/solving-business-scenarios-with-power-bi-and-dax
Time Intelligence Calculations
portal.enterprisedna.co/p/time-intelligence-calculations
***** Related Support Forums *****
Cumulative Forecasting
forum.enterprisedna.co/t/cumulative-forecasting/151
Cumulative Total This Year vs Last
forum.enterprisedna.co/t/cumulative-total-this-year-vs-last/2713
Using Current Trend To Forecast Sales
forum.enterprisedna.co/t/using-current-trend-to-forecast-sales/687
For more forecasting queries to review see here…..
forum.enterprisedna.co/search?q=forecasting
It's not a forecast, it's a media. But it's a good video.
This is a good representation of a simple average forecast and a good starting point for a more detailed projection. Thanks for this video.
Hey Sam, I keep clicking on your videos concerning projections, but they all turn out to be really simplistic annualizations at best. Would you please do some more advanced tutorials on some statistical projections in Power BI either with or without using R? Love the videos though!
Will look into this for the future
Could not agree more. I want to get a timeseries forecast into a PBI measure! Why is that so impossible
Excellent video to make us know the concept of creating a predictive data model. It will be can produce a video on the prescriptive model. Thanks😊
Nice Explanation I love your practical teaching style and hard work.
Brilliant! Always a delight.
great worth to try, life saver
Hi a. satria, glad you found this video helpful! If you haven't yet, you can subscribe to our channel to see all our upcoming tutorials and announcements. Cheers!
Hello - can Power BI do different forecasting methods? in your example, you did averaging forecasting how about moving average, exp smoothing, or regression analysis? IS there a formula can be used?
Is there any way to change these axis labels to future date ?
nice, here in divide function u repeated sales 2y ago 2 times apart from 3y ago,=divide(sales ly+sales2yago+ sales3y ago,3,0)
but u wrote divide(sales ly+sales2yago+ sales2y ago,3,0) which one correct.
Hello Sravan Kumar,
thank you for posting your query onto our channel.
Well, yes the logic that you've specified is correct. It's just a typing error on our side while referencing the variable into the measure. We'll try and correct that in our video.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/
Not sure, if this me but seems like when you are using Divide dax function you are using sales2Yrsago twice than using sales3Yrsago but thank you for sharing such a cool technique.
Great! im learning A LOT with all your teachings... thanks!!!
That's great
Awesome
This was very helpful
Thank you
Hello from Brazil! I have a question. Why did he use sales2YrsAgo twice instead of using 3YrsAgo? Please, I am just tryna understanding this calculation as I am about working on this field.
Hi Luis,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well, it's just the typing error in the video and "Sales2YrsAgo" has been referenced twice by mistake. In your scenario, you need to implement the "Sales3YrsAgo".
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful! You can subscribe to our TH-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Can I forecast with different scenarios? For example, can I make a growth rate selector, so that who is using the file can select different ones and see their effect on the data? And can I make it affect different data? For example, can I forecast the revenue and then set all the other data like costs to react to it and change automatically?
Hi Renan,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well, you can surely implement as many simulations you want in your Power BI report, as per the requirements. In case, you're an EDNA member, then you'll also have an access to our paid content wherein we've specifically created dedicated courses pertaining to those topics such as - "Scenario Analysis", Budgeting and Forecasting", "Financial Reporting", etc. You can also visit our Enterprise DNA website for more information and with an on-going offer you can even avail a discount of upto 40% on your yearly subscription.
For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful! If you haven't yet, you can subscribe to our TH-cam channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Can you do this if you update data monthly I.e cash forecast not based on 3 yr avg more mix of actual ytd plus known changes.
How would you model the budget dynamically so that you a) give more weighting to newer products vs older ones and b) model the growth steadily over the year i.e. growth occurs gradually over the year rather than all at once on Jan 1st etc
Thanks Sam. Just a heads up, your average calculation referenced the previous 2 year Var twice instead of the 3 year Var.
ahhh, sorry working too fast. Hopefully everyone can get the idea. Chrs
Sam thanks so much for your answer, do you have a video creating your date table???
Yes - th-cam.com/video/KLfOOIAqfkQ/w-d-xo.html
Hi Sam!
Many thanks for such great tutorials. I really enjoy the way you explain!
May I ask, how is the divide function working here? Looking into minute 6,32 for 17/01/18-> (50162+22460+11492)/3 =28038 which deviates from PBI calculation of 31694. Am I missing something?
Thank you in advance!
Kind regards, Cecilio.
Hi Sam,
How can we forecast on the basis of existing growth rates ? if suppose my cumulative growth rate is 13%. What would be the dax formula to add the existing growth rate?
Hello Rehan Shaikh,
Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results pertaining to the optimization without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing alongwith the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Alongwith it, you can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Happy Learning!!!
Thanks and Warm Regards,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Hi great work..
I have 14k different items and need to forecast month vise..plz advise.
Hi Shiva,
Absolutely no reason why you couldn’t extend this technique for your work.
Be sure to check out the vids below for some cool techniques around implementing this.
th-cam.com/video/7VKU-dvQv3c/w-d-xo.html
th-cam.com/video/dbtSbsg4VKg/w-d-xo.html
Be sure to check out the Enterprise DNA forum for any specific queries, you can also post PBIX files so users can provide solutions.
forum.enterprisedna.co/
Regards
Enterprise DNA
What time intelligence techniques could be added to this to take into consideration matching same day of week when looking back to previous years, before projecting to same weekdays in forecasted periods? Add to this a table of known dates for specific holidays fall year to year and you’ve got a reliable forecast you can plan resourcing from at a daily level!
Hi Wayne,
Thanks for commenting and great idea. The possibilities really are endless with what you can achieve in Power BI.
Regards,
Enterprise DNA
Hi Sam, great video!
I would like to know how to compare today's results vs yesterday results and and also compare yesterday's results vs the day before and so on. My problem is that the data I want to compare is a measure data, not a column data. Any thoughts?
Not exactly sure what you mean by measure data? All you need is a date table and to use the DATEADD function for all of those things you require.
This is very similar to what I am trying to do with a forecast, but my monthly data is not calculated in a formula, it is just long form data that has been unpivoted from another table that is in wide format. How can I use this same kind of calculation to forecast what I need?
I run into an issue when I use the Calculate function and where you put in Total Sales I need something that is not an expression as I am just using long form data. Thank you for your help.
Hello Spencer,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
We've a dedicated course available onto our education portal - "Budgeting and Forecasting" which covers extensive techniques. Below is the link of the course provided for the reference. As well as you can also go through our membership brochure over our website.
Hoping you find this useful. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
portal.enterprisedna.co/courses/enrolled/313969
enterprisedna.co/
I have forecasted data in excel , how to import it in power bi
Hi, one question here, if I do not have daily data but only have monthly data, how you suggest me to do this? when I have only one day per month having sales data.
I am not even able to use ADDDATE() to create the sales LAST YEAR (if I use bi-relationship between my calendar table with my main sales table )
any suggestion?
thank you so much!
Hi Celine - DateAdd should work even there is only one date per month in Sales table.
Try Changing Relationship b/w Date and Sales table to Single and Cardinal to One-Many.
If issue still not resolve, raise a ticket at DNA forum forum.enterprisedna.co/ along with sample PBIX file.
Thanks for the video. 👍 I think if you could show a few confidence levels and delineate live data from the forecast on the graph by colour this would make a great tutorial (maybe next one?). I done this with R in Power BI but never tried with DAX, just interested if this can be done easily in dax or M (without the need for programming ninjitsu). 😁
That can certainly be done and good ideas
So I just saw this, and I found an error.... on minute 6:15 you selected the Sales2YrsAgo VAR instead of the Sales3YrsAgo VAR
Yes sorry about that, this has already been picked up earlier as per a comment below. Hopefully you can work around the error. Thanks
Where can i find this database
Hi there, I already have a sales data for my Unit groups of previous 2 years, and i'm forecasting using excel forecast function, but wanna know more about BI. My data is different from what you posted. Is it ok for you to share your email?
Sam thanks for the video. How my date table can go to 2018 if it is linked to the sales table that only goes until the year that we have transactions (2017) ??????
Use the query editor to create a table that's larger. Your current way just isn't going to work by the sounds of it.
@@EnterpriseDNA Thanks a lot Sam for your wonderful presentations. I am still in the dark on how the 2018 date surfaced in your report. Meanwhile I followed your date table creation. Thanks.
I have been able to solve the problem. Thanks a lot.
@@fobembe How did you solve it ?
Good, but would prefer one that is flexible enough to have different forecasts for the user.
Hi Blessing Chivave, appreciate the suggestion. We will put this on our list.