Forecasting Logic in Power BI with DAX
HTML-код
- Опубликовано: 27 май 2017
- Use these techniques to create forecasts based on historical information and then dynamically track results to budgets or forecasts into the future. See if you will make budget across multiple dimensions.
****Video Details****
0:17 - Introduction
2:10 - Creating A Forecast
5:14 - Working Out The Cumulative Forecast
7:51 - Forecast Versus Sales
9:13 - Forecasting By Product
**** 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/forecas...
Creating Forward Forecasts In Power BI Using DAX
blog.enterprisedna.co/create-...
Create A New Table In Power BI: How To Implement Budgets & Forecasts Automatically Using DAX
blog.enterprisedna.co/create-...
**** Related Course Modules ****
Budgeting & Forecasting
portal.enterprisedna.co/p/budg...
DAX Formula Deep Dives
portal.enterprisedna.co/p/dax-...
Mastering DAX Calculations
portal.enterprisedna.co/p/mast...
**** Related Support Forums ****
Predicting Future Years
forum.enterprisedna.co/t/pred...
Using Current Trend To Forecast Sales
forum.enterprisedna.co/t/usin...
Maintain Forecast DAX Calculation In Different Context
forum.enterprisedna.co/t/main...
For more forecasting logic queries to review see here…..
forum.enterprisedna.co/search... 
Наука
***** Related Links *****
Forecasting Analysis Techniques In Power BI With DAX
blog.enterprisedna.co/forecasting-techniques-in-power-bi-by-using-dax/
Creating Forward Forecasts In Power BI Using DAX
blog.enterprisedna.co/create-dynamic-forward-forecasts-in-power-bi-w-dax/
Create A New Table In Power BI: How To Implement Budgets & Forecasts Automatically Using DAX
blog.enterprisedna.co/create-and-set-budgets-automatically-in-power-bi-using-dax/
***** Related Course Modules *****
Budgeting & Forecasting
portal.enterprisedna.co/p/budgeting-and-forecasting
DAX Formula Deep Dives
portal.enterprisedna.co/p/dax-formula-deep-dives
Mastering DAX Calculations
portal.enterprisedna.co/p/mastering-dax-calculations
***** Related Support Forums *****
Predicting Future Years
forum.enterprisedna.co/t/predicting-future-years/2112
Using Current Trend To Forecast Sales
forum.enterprisedna.co/t/using-current-trend-to-forecast-sales/687
Maintain Forecast DAX Calculation In Different Context
forum.enterprisedna.co/t/maintain-forecast-dax-calculation-in-different-context/5100
For more forecasting logic queries to review see here…..
forum.enterprisedna.co/search?q=forecasting%20logic
Thank you so much for your vídeos! it is so useful S2
Good Morning!! A help.
I need to set up an income statement for the year (DRE) with a verifical analysis for each level, that is, each level must be 100% for the level below:
The Gross Revenue Value must be fixed only in the cost / expense groups, the values in the cost / expense groups must be fixed in the cost / expense subgroup and the values in the cost / expense subgroup in the analytical accounts. This way I can do the proportion in each level of the DRE ... but I am BREAKING THE HEAD OF THIS
Congratulations on the channel, excellent !!!
Sam it's amazing 👏
Great video! Thank you so much! I wonder how you make Power BI panel having dark theme ?
Hi Tam,
It's nothing fancy just make sure you have updated to the latest version of Power BI.
Don’t forget to checkout the Enterprise DNA forum for any specific queries.
forum.enterprisedna.co/
Regards,
Enterprise DNA
Hi Sam, thank you so much! Your videos are wonderful.
However, is it possible if you demonstrate how to use DAX to perform an exponential smoothing forecast? That's what my company have always been using, and I need to translate that into Power BI.
Will add this to the list.
Hi! It is interesting.
BUT, are you sure that a data of the last year is a forecast?
I'm not sure why it can't be. This is just a technique anyway, there are many many different ways you can create forecasts. This is just one.
I am also in the same page ? How come Last Year is a Forecast. It could be really effective if we would have seen forecast for 2017. which is not present.
Yes I had that thought too. It’s not possible to assume Sales LY could be the basis of the forecast for the following year. The numbers are too high including the cumulative numbers
I would have preferred a measure where which would have taken into consideration a percentage of the current sales based on daily activity
nice but very simplified forecasting process that would be. In real life, forecasts are much more robust.
Hello, again thanks a lot for your videos. Really amazing job.
I have a question, I need to build a structured dashboard using different level of precision. Meaning for exemple that we start with the whole group and drilling down to different markets and then to different offices. How would you solve this matter? I know it is very complex, but it would be very powerfull to build this :-) Maybe you could do a small video to explain how the structure could look like :-)
Thanks again
Mark
Really you would solve this by building an effective model. It's actually not that complex if you get the setup right
Very powerful instruction thank you Sam. I concur with comments below re. forecast numbers: the example would be better illustrated if forecast amounts were unique - not a copy of the prior year's amounts. Also, I believe your tracking chart might be better illustrated if the formula were Sales vs Forecast - I am interested in how my cum sales is tracking against the forecast - if sales is less-than forecast (as it is from Jan to May) then I would expected the delta graph to be negative during that period. Still,,,,this is fascinating instruction - thanks very much.
@Enterprise DNA Team thanks Sam. I agree. Ive seen some youtube tech authors post pop up messages to clarify, add flavor, point people to other content...
Great content..very high quality.
Thank you for a through video. I am trying to calculate the monthly forecast using monthly data that is in long form from an unpivoted excel table that does not use formulas to calculate the monthly data. The built in forecasting in Power BI is not what I need as I need the future spending numbers. Is there a video that goes into that?
Hello Spencer,
Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
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.
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. Also, make sure that you're subscribed to our RUclips 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/
ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
www.linkedin.com/groups/12004506/
portal.enterprisedna.co/courses/enrolled/313969
enterprisedna.co/
Hi Sam,
Love your tutorials. The business I work for closes at the weekend, so when I use the ISBLANK function I get gaps in the area chart. Any tips on how I can overcome this..
Thanks Dave. The key to solve this one is to restrict the dates table to only weekdays. You can do this by using filter. What you want to likely do in your date table is have a weekday number column, then filter out the Saturday and Sunday numbers...FILTER( Dates, Dates[Date] 6 || Dates[Date] 0 ). It will depend on what those numbers are but in the date table I use it is 6 & 0.
Enterprise DNA Thanks a million.. I'll give it a go. Really appreciate the response.
No worries
is it possible to create our own slicers suppose 1.current QTD vs prior QTD 2. Current MTD vs Prior MTD and display the related visuals accroding to that?
Yes, check out the techniques showcased here - ruclips.net/video/cgPnnPBwAqQ/видео.html
This is exactly what you need to do for this
Hi Sam, I have a question and I need your advice. I have data up to say june 2017. I want to get the data for one year from june-16 till june 2017 and shift date context so it becomes june 2017 - june 2018, to create a basic forecast. and then using the extra table for % growth I can show increase in percentages. my question is how to push the data forward in the time frame?
I believe the another video titled - Advanced Forward Forecasting Techniques in Power BI with DAX, should explain how to do this if I'm understanding you correctly.
Please how do u calculate saisonality ?
Why it doesn't look like a forecast to me? IFrom the DAX it looks like you already have data for 2016 in your tables. I think you are essentially calculating sales for 2016 using the 2016 sales data in the table. Not sure how the DAX for 2016 Forecast is projecting the values that is not there in the tables based on historical data. Correct me if I am wrong.
one question here is if my ordinary date time line is only 2017, but I want to show the forecast in 2018. How shoud I use the method to do that?
Hi Celine - For ForeCast of 2018 based on 2017, your Date table should have dates for 2018. Please check below video for ForeCast using Historical data.
ruclips.net/video/vq3VOERJw7s/видео.html
For any specific query, raise a Ticket at DNA Forum forum.enterprisedna.co/
Hi, Thanks for the video. I am facing a problem. I have a data set with 12 months data of everyday sales of 5 regions and 16 products. I need to forecast that will indicate overall sales prediction for next three months, there must be a drill down capability of region and products. i.e. in the same graph i need to show forecast for each and every region and product using slicer. The data set is like this. everyday, more than one region, and more than 1 product have sales data.
Please help me with this, i am stuck with this problem since last 2 weeks
Thank You
These seems quite standard and just requires some sound underlying Power BI knowledge. Certainly check out the beginner's guide courses here to get you going - portal.enterprisedna.co
How is Sales LY value for 2016 considered as Forecast? Am I missing something? Sales LY and 2016 Forecast both shows exactly the same value. I am confused. Can someone explain how this is predicting the data. This is not using any prediction if I am correct its simply getting the last year sales and plotting it onto 2016.
Hi Varun,
The point here is to demonstrate the possibilities of what’s possible with Power BI. You could quite easily import any advanced forecasts into Power Bi, you could also easily extrapolate and build your own forecast based on previous years with advanced logic.The point of this video was to demonstrate some of the techniques rather than focus on different forecasting methods.
Regards,
Enterprise DNA
I liked almost all of your videos, this is also good but you aren't calculating forecast in anyway. It's just that you have brought up some previous data against current data. It should follow some forecasting model to be called as forecast. But nevertheless it a good technique.
Although I like the video contents, I think the term 'Advanced Forecasting' is a bit misplaced. The technics and formulas are nice, but you're 'just' plotting last year sales into the chart. No advanced forecasting analytics or technics used. Besides this, very usefull, and well explained!
Thanks for your note. I have to disagree unfortunately with your comment. Regarding DAX formula, this is a combinations of some advanced patterns and logic that most will generally not have combined before. Everything is up to interpretation though so I respect your thoughts.
I totally agree with you on the used pattern and logic combined. Great work! It's just the definition of 'forecast'. I was looking for a forecast pattern, and therefore watched your video. I believe, in most companies, a forecast isn't just the last year sales values, but it also contains a possible growth, or correction, or prediction. Nevertheless, thanks for your reply, and keep up the good work!
Those are some good idea. Look out for some upcoming videos that go through how to add that sort of logic in.
Enterprise DNA yes, it would be nice to have example with forecasting based on trend of YTD or something similar
Literally, forecasting, isn't just last year sales. Good video anyway!
Can you use this technique to go multiple years into the future?
Hello David Price,
thank you for posting your query onto our channel.
Well you can definitely use this type of technique that's been showcased into the video. And we're also providing you a link from where you can learn more about the "Forecasting Techniques".
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!!!
portal.enterprisedna.co/courses/enrolled/313969
forum.enterprisedna.co/
In some paper they have asked
Which is not a forecasting technique
Regression
Time series
HOLT & WINTER method
Exponential smoothing
Which option I need to select ??
Hello jayant,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
As per our research, "Exponential Smoothing" should be the answer. We request you to please conduct your own research and check out the books on "Forecasting Techniques".
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 RUclips 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/
ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
www.linkedin.com/groups/12004506/
@@EnterpriseDNA Thank you so much for your brief information...