Create Automatic Forecasts From Historic Data in Power BI using DAX

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

***** Related Links *****
Forecasting Analysis Techniques in Power BI with DAX
Explore Forecasting Logic In Your Power BI Models Using DAX
Create Dynamic Cumulative Totals Using DAX In Power BI

***** Related Course Modules *****
Budgeting & Forecasting
Solving Analytical Scenarios w/Power BI & DAX
Time Intelligence Calculations

***** Related Support Forums *****
Cumulative Forecasting
Cumulative Total This Year vs Last
Using Current Trend To Forecast Sales
For more forecasting queries to review see here…..

[EnterpriseDNA]

Related Links
Forecasting Analysis Techniques in Power BI with DAX
Explore Forecasting Logic In Your Power BI Models Using DAX
Create Dynamic Cumulative Totals Using DAX In Power BI

Related Course Modules
Budgeting & Forecasting
Solving Analytical Scenarios w/Power BI & DAX
Time Intelligence Calculations

Related Support Forums
Cumulative Forecasting
Cumulative Total This Year vs Last
Using Current Trend To Forecast Sales
For more forecasting queries to review see here…..

[Hitman-ossp]

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

[larl]

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!

[magrelo]

It's not a forecast, it's a media. But it's a good video.

[rybltbt]

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?

[sravankumar]

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.

[luisfernandomacedo]

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.

[aishwarya]

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

[P-ok]

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.

[TanKengGhee]

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😊

[kanwalsingh]

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.

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

[RehanShaikh-iphh]

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?

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

[rational]

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

[spencerpitcher]

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.

[theoh]

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

[vinaykumargupta]

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

[celinexu]

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!

[prachigupta]

I have forecasted data in excel, how to import it in power bi