{"id":3960,"date":"2022-01-28T11:26:05","date_gmt":"2022-01-28T19:26:05","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3960"},"modified":"2022-01-28T11:26:08","modified_gmt":"2022-01-28T19:26:08","slug":"how-to-create-forward-forecasts","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3960","title":{"rendered":"How to Create Forward Forecasts"},"content":{"rendered":"\n<p>In this blog, we\u2019re going to go over how you can create&nbsp;automated forecasts&nbsp;from historic data in&nbsp;<strong>Power BI<\/strong>. This is common in businesses to make forecasts and budgets.&nbsp;<strong>You may watch the full video of this tutorial<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"Create Automatic Forecasts From Historic Data in Power BI using DAX\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/vq3VOERJw7s?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>Now, have you ever wanted to&nbsp;<strong>create automatic forecasts based on historical information<\/strong>? In the past, this was relatively difficult to do using tools like Excel, but you can do this easily inside&nbsp;<strong>Power BI<\/strong>.<a href=\"https:\/\/portal.enterprisedna.co\/p\/ultimate-power-bi-resource-collection\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>Download Free Resources Here<\/strong><\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/Create-Automatic-Forecasts-From-Historic-Data-in-Power-BI-using-DAX-3344244653-1574988563971.png\" alt=\"\" class=\"wp-image-89167\"\/><\/figure>\n\n\n\n<p>Historically, you might have retrieved information and summarized it in a table, and used that as a benchmark. But in the video, I show you how you can&nbsp;<strong>create benchmarks and forecasts dynamically<\/strong>. This is a more effective and efficient way to project numbers forward and enables you to&nbsp;<strong>compare how you\u2019re actually performing against a prior period or a combination of prior periods.<\/strong><\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>What we\u2019re going to do is look at historical time periods and use those as values in our equation to project a forward forecast.<\/p><\/blockquote>\n\n\n\n<p>I\u2019m going to utilize&nbsp;<strong>time intelligence functions<\/strong>&nbsp;in Power BI to showcase how you can do this in a very dynamic way.<\/p>\n\n\n\n<p><strong>By combining multiple techniques in&nbsp;Power BI using DAX, you can achieve these really great insights.<\/strong>&nbsp;In this case, we\u2019re just drilling into forecasts and trying to create forward benchmarks, so that we can compare our actual results against something that actually makes sense.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"how-to-create-automated-forecast-from-historic-data\"><strong>How To Create Automated Forecast From Historic Data<\/strong><\/h2>\n\n\n\n<p>In a lot of cases, your forecast is derived from your historical results. So I\u2019m going to show you how you can quickly grab historic data, consolidate it, and then create a forecast from it, which is still align to your entire data model.&nbsp;<\/p>\n\n\n\n<p>Let\u2019s assume that we already have say some sales information already and we have our&nbsp;<strong>Sales&nbsp;<\/strong>calculation here.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/1.39-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1-1024x387.png\" alt=\"\" class=\"wp-image-89170\"\/><\/figure>\n\n\n\n<p>So we need to find a way to project forward to 2018. We want to work out our sales forecast in 2018. If you look at this filter down on the right-hand side, I\u2019ve actually filtered for only 2018, so we\u2019re only looking at 2018 here.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/2.02-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x613.png\" alt=\"\" class=\"wp-image-89171\"\/><\/figure>\n\n\n\n<p>Now we create another measure table by clicking on Enter Data and it will give you the option to create a measure table. Let\u2019s call this table&nbsp;<strong>Sales Forecasting<\/strong>. If you can get into the habit of creating these measure tables, it would benefit you immensely in terms of organizing your model.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/2.16-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x576.png\" alt=\"\" class=\"wp-image-89172\"\/><\/figure>\n\n\n\n<p>Next is we create a new measure using time intelligence functions to make our sales projections from 2017\u2019s numbers. So we go to New Measure, and let\u2019s call this the&nbsp;<strong>Sales LY<\/strong>&nbsp;and go&nbsp;<strong>CALCULATE&nbsp;<\/strong>by&nbsp;<strong>Total Sales<\/strong>.&nbsp;We could do the same for any any of our metrics, but in this example, we\u2019re going to work on Sales.<\/p>\n\n\n\n<p>And we need a&nbsp;<strong><a href=\"https:\/\/blog.enterprisedna.co\/the-dateadd-function-the-best-and-most-versatile-time-intelligence-function-in-power-bi\/\" target=\"_blank\" rel=\"noreferrer noopener\">DATEADD<\/a><\/strong>, which is one of the best time intelligence function just because of the flexibility you have with it. Then, we jump back one year here (<strong>-1<\/strong>), and put our interval (<strong>YEAR<\/strong>) up here.&nbsp;Then, push Enter.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/3.22-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x76.png\" alt=\"\" class=\"wp-image-89173\"\/><\/figure>\n\n\n\n<p>If we grab this and drag it to the table, we\u2019re going to see that we are now&nbsp;<strong>projecting forward all of the data from 2017<\/strong>. So we got our first column of information of the three that we\u2019re going to calculate today.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/3.26-Create-Forward-Forecasts-in-Power-BI-Using-DAX-3941407874-1574988671339.png\" alt=\"\" class=\"wp-image-89174\"\/><\/figure>\n\n\n\n<p>In this sample scenario, we have to jump back two years as well because we want to work out three years forecast.&nbsp;So to do the second year, we simply copy the pattern, and just make a couple of adjustments.<\/p>\n\n\n\n<p>We change the name of the measure and the parameter inside, and we\u2019re now projecting our sales from two years ago. We will also do exactly the same for the 3 years ago.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/4.11-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x66.png\" alt=\"\" class=\"wp-image-89175\"\/><\/figure>\n\n\n\n<p>And now we have three years of information that we can now create into our forecast.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/4.48-Create-Forward-Forecasts-in-Power-BI-Using-DAX.png\" alt=\"\" class=\"wp-image-89176\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"using-variables-to-create-one-measure-for-a-3-year-forecast\"><strong>Using Variables To Create One Measure For A 3-Year Forecast<\/strong><\/h2>\n\n\n\n<p>There\u2019s another way to do this in an efficient way to create a Power BI forecast. We\u2019ll use variables to create one measure, instead of three, and get exactly the same result that we seek.<\/p>\n\n\n\n<p>So we go to New Measure once more and for this, let\u2019s call it the&nbsp;<strong>Sales Forecast<\/strong>. We go&nbsp;<strong>VAR&nbsp;<\/strong>(variables), then&nbsp;<strong>Sales LY<\/strong>&nbsp;on the next line. We do the same on the next couple of rows for&nbsp;<strong>2 and 3 years ago<\/strong>.<\/p>\n\n\n\n<p>After which, we can jump down and go&nbsp;<strong>RETURN,&nbsp;<\/strong>and here\u2019s where we can put in the logic. We use&nbsp;<strong>DIVIDE&nbsp;<\/strong>with our three years of data, so we&nbsp;<strong>sum up Sales LY, Sales 2 years ago, and Sales 3 years ago<\/strong>.&nbsp;Then, we&nbsp;<strong>divide it by 3<\/strong>. We\u2019ll also put our&nbsp;<strong>alternative result,which is 0<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/6.21-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x329.png\" alt=\"\" class=\"wp-image-89177\"\/><\/figure>\n\n\n\n<p>We just put all three of the measures we made earlier inside of variables, we get the same result. I highly recommend this, as it is more efficient. We have now our sales forecast and we have an average of all of these three.<\/p>\n\n\n\n<p>Moreover, we want to see some increase in our sales, right? So let\u2019s do a forecast to showcase&nbsp;<strong>a five percent growth rate<\/strong>. To do this, we simple add a&nbsp;<strong>FACTOR&nbsp;<\/strong>in our formula, and then&nbsp;<strong>multiply the last row by the factor<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/11-8-1024x413.png\" alt=\"\" class=\"wp-image-89243\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"creating-the-visualization\">Creating The Visualization<\/h2>\n\n\n\n<p>Once all the needed formulas are created, we turn this into visualizations and see clearly our Power BI forecast. And we now have a virtual sales forecast that showcases how much we need to make every single day to reach our forecast.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/7.26-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x597.png\" alt=\"\" class=\"wp-image-89179\"\/><\/figure>\n\n\n\n<p>We can also put this inside a&nbsp;<strong>cumulative total pattern<\/strong>. So we go and create a new measure, and call it&nbsp;<strong>Cumulative Forecast<\/strong>.&nbsp;On the next line, we put in&nbsp;<strong>CALCULATE Sales Forecast<\/strong>.&nbsp;Then, go&nbsp;<strong>FILTER&nbsp;<a href=\"https:\/\/blog.enterprisedna.co\/percentage-of-total-using-all-and-allselected\/\" target=\"_blank\" rel=\"noreferrer noopener\">ALLSELECTED<\/a><\/strong>&nbsp;by&nbsp;<strong>Dates<\/strong>.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/8.08-Create-Forward-Forecasts-in-Power-BI-Using-DAX.png\" alt=\"\" class=\"wp-image-89180\"\/><\/figure>\n\n\n\n<p>Once we have that, we put it down here and make it a cumulative total, and now we got a cumulative forecast that we could measure up&nbsp;<strong>cumulatively versus our actual results<\/strong>&nbsp;as soon as we got into 2018.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/8.16-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x430.png\" alt=\"\" class=\"wp-image-89181\"\/><\/figure>\n\n\n\n<p>The coolest way about doing this is that it&nbsp;<strong>links up to the data model<\/strong>. So your forecasts can be filtered by anything in the data model because they derive from historical information that sits on a table within the data model.<\/p>\n\n\n\n<p>With this, we can easily go and&nbsp;<strong>filter by Product Name<\/strong>&nbsp;here. We grab our&nbsp;<strong>Sales Forecast<\/strong>&nbsp;for the Product name and now we have forecast my products.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/8.53-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x467.png\" alt=\"\" class=\"wp-image-89182\"\/><\/figure>\n\n\n\n<p>This also enables us to select a particular product, say Product 47. And as we change the filters here we can see how much we need to sell per day for this product, and\/or see its cumulative result clearly.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/blog.enterprisedna.co\/wp-content\/uploads\/2019\/11\/9.20-Create-Forward-Forecasts-in-Power-BI-Using-DAX-1024x576.png\" alt=\"\" class=\"wp-image-89183\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"conclusion\"><strong>Conclusion<\/strong><\/h2>\n\n\n\n<p>In this tutorial, we\u2019ve gone through a lot, such as time intelligence and cumulative total patterns to create a Power BI forecast.<\/p>\n\n\n\n<p>I\u2019ve seen this asked a number of times in comments and forums, and I just wanted to showcase how it\u2019s relatively straightforward to create a forecast from historical information in Power BI.<\/p>\n\n\n\n<p>I hope you find that you can benefit from this and implement this in your own environments. Find some way to utilize some of the techniques that we\u2019ve gone through in this tutorial.&nbsp;Dive into the video below and try to use the techniques I showcased in your own development work.<\/p>\n\n\n\n<p>Ref: https:\/\/blog.enterprisedna.co\/create-dynamic-forward-forecasts-in-power-bi-w-dax\/<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog, we\u2019re going to go over how you can create&nbsp;automated forecasts&nbsp;from historic data in&nbsp;Power BI. This is common in businesses to make forecasts and budgets.&nbsp;You may watch the full video of this tutorial. Now, have you ever wanted to&nbsp;create automatic forecasts based on historical information? In the past, this was relatively difficult to <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3960\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[1074,1075],"class_list":["post-3960","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-forward-forecasts","tag-forward-forecasts-power-bi"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3960","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3960"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3960\/revisions"}],"predecessor-version":[{"id":3969,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3960\/revisions\/3969"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3960"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3960"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3960"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}