Forecasting Logic in Power BI with DAX

[@EnterpriseDNA]

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

[@DomDiegoOliveira]

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

[@salosteysv]

Hi! It is interesting. BUT, are you sure that a data of the last year is a forecast?

[@EnterpriseDNA]

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.

[@sukhomoyb]

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.

[@naramarts1529]

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

[@jayantmishra4270]

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

[@EnterpriseDNA]

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 KZbin 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/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

[@jayantmishra4270]

@@EnterpriseDNA Thank you so much for your brief information...

[@dralupovski]

nice but very simplified forecasting process that would be. In real life, forecasts are much more robust.

[@janainacarmo1603]

Thank you so much for your vídeos! it is so useful S2

[@varundevaraj3476]

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.

[@EnterpriseDNA]

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

[@fw4693]

Please how do u calculate saisonality ?

[@thyejansman]

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!

[@EnterpriseDNA]

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.

[@thyejansman]

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!

[@EnterpriseDNA]

Those are some good idea. Look out for some upcoming videos that go through how to add that sort of logic in.

[@dragontrans]

Enterprise DNA yes, it would be nice to have example with forecasting based on trend of YTD or something similar

[@Axewayboy]

Literally, forecasting, isn't just last year sales. Good video anyway!

[@TamNguyen-jo8qn]

Great video! Thank you so much! I wonder how you make Power BI panel having dark theme ?

[@EnterpriseDNA]

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

[@spencerpitcher8441]

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?

[@EnterpriseDNA]

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

[@Zionmcclung]

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.

[@EnterpriseDNA]

Will add this to the list.

[@47pricey]

Can you use this technique to go multiple years into the future?

[@EnterpriseDNA]

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/

[@davehussey4024]

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

[@EnterpriseDNA]

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.

[@davehussey4024]

Enterprise DNA Thanks a million.. I'll give it a go. Really appreciate the response.

[@EnterpriseDNA]

No worries

[@gagansingh3481]

Sam it's amazing 👏

[@varundevaraj7424]

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.

[@markk364]

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.

[@markk364]

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

[@celinexu6598]

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?

[@EnterpriseDNA]

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. kzbin.info/www/bejne/rKKWh4J7h6-qbdU For any specific query, raise a Ticket at DNA Forum forum.enterprisedna.co/

[@pranavkumar2336]

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?

[@EnterpriseDNA]

Yes, check out the techniques showcased here - kzbin.info/www/bejne/mZizn6GGd9x0p7M This is exactly what you need to do for this

[@avijitnandy6662]

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

[@EnterpriseDNA]

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

[@npmnpm5501]

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?

[@EnterpriseDNA]

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.

[@dataworld9783]

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.

[@marklovejoy907]

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

[@EnterpriseDNA]

Really you would solve this by building an effective model. It's actually not that complex if you get the setup right