Create Automatic Forecasts From Historic Data in Power BI using DAX

Рет қаралды 105,820

6 жыл бұрын

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.enterprisedna.co/p/ulti...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ulti...
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-...
FREE - Power BI Resources - enterprisedna.co/power-bi-reso...
Enterprise DNA Membership - enterprisedna.co/membership
Enterprise DNA Online - portal.enterprisedna.co/
Enterprise DNA Events - enterprisedna.co/enterprise-d...
**** Related Links ****
Forecasting Analysis Techniques in Power BI with DAX
blog.enterprisedna.co/2019/03...
Explore Forecasting Logic In Your Power BI Models Using DAX
blog.enterprisedna.co/2019/04...
Create Dynamic Cumulative Totals Using DAX In Power BI
blog.enterprisedna.co/2019/07...
**** Related Course Modules ****
Budgeting & Forecasting
portal.enterprisedna.co/p/budg...
Solving Analytical Scenarios w/Power BI & DAX
portal.enterprisedna.co/p/solv...
Time Intelligence Calculations
portal.enterprisedna.co/p/time...
**** Related Support Forums ****
Cumulative Forecasting
forum.enterprisedna.co/t/cumu...
Cumulative Total This Year vs Last
forum.enterprisedna.co/t/cumu...
Using Current Trend To Forecast Sales
forum.enterprisedna.co/t/usin...
For more forecasting queries to review see here…..
forum.enterprisedna.co/search...

Пікірлер: 57

@EnterpriseDNA 4 жыл бұрын

***** 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

@Hitman-os1sp 5 жыл бұрын

This is a good representation of a simple average forecast and a good starting point for a more detailed projection. Thanks for this video.

@eyitayoeyitayo-lawal2331 4 жыл бұрын

Brilliant! Always a delight.

6 жыл бұрын

Great! im learning A LOT with all your teachings... thanks!!!

@EnterpriseDNA 6 жыл бұрын

That's great

@vinaykumargupta8294 5 жыл бұрын

Nice Explanation I love your practical teaching style and hard work.

@TanKengGhee 3 ай бұрын

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😊

@larl07 6 жыл бұрын

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!

@EnterpriseDNA 6 жыл бұрын

Will look into this for the future

@evang5216 6 жыл бұрын

Could not agree more. I want to get a timeseries forecast into a PBI measure! Why is that so impossible

@adeotijoshua6427 4 жыл бұрын

Awesome This was very helpful Thank you

@rybltbt 5 жыл бұрын

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?

@theoh941 5 жыл бұрын

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

@izabelaedmunds9746 6 жыл бұрын

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). 😁

@EnterpriseDNA 6 жыл бұрын

That can certainly be done and good ideas

@user-hm8wz4vp8l 3 жыл бұрын

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.

@JorgeGrisman 6 жыл бұрын

Sam thanks so much for your answer, do you have a video creating your date table???

@EnterpriseDNA 6 жыл бұрын

Yes - kzfaq.info/get/bejne/gbKWgrJ6ptfKm4U.html

@agengsatria Жыл бұрын

great worth to try, life saver

@EnterpriseDNA Жыл бұрын

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!

@aishwarya3629 4 жыл бұрын

Is there any way to change these axis labels to future date ?

@reggierevello21 6 жыл бұрын

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?

@EnterpriseDNA 6 жыл бұрын

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.

@waynekranz7813 4 жыл бұрын

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!

@EnterpriseDNA 4 жыл бұрын

Hi Wayne, Thanks for commenting and great idea. The possibilities really are endless with what you can achieve in Power BI. Regards, Enterprise DNA

@sravankumar1767 3 жыл бұрын

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.

@EnterpriseDNA 3 жыл бұрын

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/

@shivaandure8130 4 жыл бұрын

Hi great work.. I have 14k different items and need to forecast month vise..plz advise.

@EnterpriseDNA 4 жыл бұрын

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. kzfaq.info/get/bejne/bbx7iJCV27faY5c.html kzfaq.info/get/bejne/msikhsWkzJq6e5s.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

@RenanLeonardidaSilva Жыл бұрын

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?

@EnterpriseDNA Жыл бұрын

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 KZfaq 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/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

@rational2141 6 жыл бұрын

Thanks Sam. Just a heads up, your average calculation referenced the previous 2 year Var twice instead of the 3 year Var.

@EnterpriseDNA 6 жыл бұрын

ahhh, sorry working too fast. Hopefully everyone can get the idea. Chrs

@RehanShaikh-ip9hh 2 жыл бұрын

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?

@EnterpriseDNA 2 жыл бұрын

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 KZfaq 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/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

@JorgeGrisman 6 жыл бұрын

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

@EnterpriseDNA 6 жыл бұрын

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.

@fobembe 4 жыл бұрын

@@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.

@fobembe 4 жыл бұрын

I have been able to solve the problem. Thanks a lot.

@cacafutis 2 жыл бұрын

@@fobembe How did you solve it ?

@luisfernandomacedo1451 2 жыл бұрын

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.

@EnterpriseDNA Жыл бұрын

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 KZfaq 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/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

@spencerpitcher8441 2 жыл бұрын

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.

@EnterpriseDNA 2 жыл бұрын

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 KZfaq 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/ kzfaq.info/love/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ portal.enterprisedna.co/courses/enrolled/313969 enterprisedna.co/

@amilkargonzalez152 6 жыл бұрын

So I just saw this, and I found an error.... on minute 6:15 you selected the Sales2YrsAgo VAR instead of the Sales3YrsAgo VAR

@EnterpriseDNA 6 жыл бұрын

Yes sorry about that, this has already been picked up earlier as per a comment below. Hopefully you can work around the error. Thanks

@RaviKumar-mk4mj 4 жыл бұрын

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?

@ceciliocbaroperez8815 4 жыл бұрын

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.

@kanwalsingh2802 2 жыл бұрын

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.

@celinexu6598 4 жыл бұрын

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!

@EnterpriseDNA 4 жыл бұрын

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.