How to forecast data using DAX in Power BI? | Forecast Data in Power BI | DAX | BI Consulting Pro

hello everyone welcome to bi consulting chrome today is sunday sunday means tax sunday and every sunday we bring something new in dax so in today's video we are going to discuss about forecast data using tax many of you have asked us earlier how we can forecast data using dax not by using conventional way in power bi that you can use the analytics pane and can do the forecasting of your data but what if you have your own logic to forecast the data you have your own calculation to perform that so you can convert that calculation into dax and then you can use it in today's video i am going to present you one of the method that you can use and then you can use that calculation into your dax and you can calculate your forecast of the data by the end of this video you will see similar to this one so this is what i have already created using decks calculations and i have created certain measures and you can also do the same so stay tuned till the end of this video and i'll let you know how to do it so the very first question comes what is our data model today's data model is very very simple i have one date table or i'll call it a year table where i have column where i have mentioned all the years i have entered the data manually so you can also do it or if you have already a date table so you can also use the year column from that date table then i have another sales table where i have year field and then i have another field which is sales where i have mentioned some of the sales again you can also enter the same data yourself or you can use whatever table you have where you have some of your values which you want to forecast the next part would be the calculations so this is the most important part that you need to understand for today's method there are two steps involved in the very first step what we are going to do we are going to see how to calculate the compound annual growth rate so this is basically a formula that we are going to use to calculate the cagr and the formula would be your end value basically your last year then start value would be your start here then you have to use it as the power of one divided by period so how many years are gonna be into this one and then at last we are gonna detect one from that total calculation and you can see at the bottom in the second step over here i have mentioned that calculate forecast for 2018 you can see the revenue of 2017 because we are going one year back and then we are using power one plus cagr so guys you have to be very much careful about the calculations and these calculation i have converted into dex formula and that next formula i'm using into my measure and that's how we are going to calculate the forecast so let's see how to do that right now i'm into my power bi desktop application and here you can see right now there's nothing but if we will go into my data model there are two tables one is the sales table another is the year table and there's a relationship 101 but maybe if you are using your date table that can be one-to-many relationship which is the ideal for the star schema now if i take you into my data view here you can see this is my year table where there is only one column by the name eo and my sales table has two column one is for year another is for sales and it's a perfect example to demonstrate you how you can forecast your data using tax now let's come into our report view and now we have to create certain measures that we are gonna create over here and we will start with the very first one which is gonna be our first year so what we have to do we have to come into our fields view here we'll see our tables are there so i'm going to create my all the majors into sales table and i'll expand this one now let me create my very first major which is going to be first year because we have to calculate our start value that would be our first year now in order to calculate the first year we need first non-blank value and for that we can use the text function first non-blank so this is our text function which is going to returns the first value in the column for which the expression has a known blank value and now we have to use our table the main table where our year values are there because we have to calculate based on that so let me just use it all because i'm removing all the filters if even there is any filter and then we can use this year column and what we have to calculate over here this is gonna be our initial value for the first year and here we are gonna calculate the sum of all the sales so calculate sum of sales from the sales table so guys what we have done over here what is the value of for that particular year and similarly i'm going to calculate the value for the last year's one now let me copy this and i'm gonna use the same for the last year and only what i'm gonna change over here i'm going to change the text function here you can paste it and we just need to change this first non-blank to last non-blank why we are doing this because we have to calculate two values but before that we have to rename it as well so guys now we have calculated our first year value and last year value third step would be over here if you will see we have calculated the start value and value but now we have to calculate the cagr that is our compound annual growth rate so that would be in percentage and we are gonna calculate it so let's start with that so again i'm gonna come to my sales table here i'm going to create a new measure here we have to create couple of variables first would be last year and that would be our last year value that we have already calculated similar to this i'm gonna calculate the another variable which would be my first year now we have calculated these two values but we also need to calculate the interval or the time difference between first and last value so what we can do we'll say variable another variable that is going to give us number year that would be equals to last year minus first year value so here we have calculated it so now we have our variables now we are going to apply the same formula over here that we just explained over here this formula the same formula we are going to apply over there so let's see how to do that so the very first we have to return our values so for that let's write the written statement and here so now you see i have divided it the last year value to the first year but this more we have to write it down as the terms of power as well so for that we have a special formula index that we can use so what i can do over here i'll write power so you can see the power so whenever we have to write some numbers power like 2 raised to power 3 equals to 8 so that's the same thing we are gonna do over here i'm going to start it from there and here i'll come down let me close it there and here comma power would be one divided by number of years that means the that period the interval over here so over here we can use this variable number of here so let me just copy this and paste it over here so that becomes our formula but still we have to deduct one so what i can do i'll come down and i'll say minus one so this is gonna be our formula for cagr let me repeat it again first we calculated the last year value which is going to be last year similar to the first year then we calculated the difference between these two values that means last year and first year and then we are just putting our values into that formula so index it's a bit different but more like it's the same so now this has been completed if you have any question or this you can ask me and i'll try to help you out so let's move forward now we have calculated our cagr the last part remaining that we have to calculate the forecast so let's calculate that one too so in order to forecast again i have to create a new measure i'm gonna give it a name forecast so here also we are gonna use couple of variables and here the very first variable would be our last year so last year it's gonna be same as the last year let's use this over here that another variable would be number of years so let's say number of years so here what we are going to do we have to catch the selected value so that we can know from where to start or where not to start and that's what we are going to do over here so suppose anyone is selecting some values from the date table where we have the year column like 2013 14 or 16 and all so that's what we have to do over here so what i'm gonna do i'm gonna use the selected value next function and if you are not aware about the selected value text function i have already explained it in my previous videos and there is already a video on our youtube channel please go and check that out i'll also provide the link in the description section for the tech series and you can find it over there too so we have to first get that value that has been selected in the slicer or in the context that we are talking about and that would be from the year column of the year table and we have to just deduct the last year value from this one that's it guys now we have to return our result over here so result is gonna be in the return statement and here we are gonna use the if condition if again i'm gonna say my selected value this one is greater than my last year value so i have already created a variable of the last year or you can use that directly as well so first we write if if my selected value that is the year selected is greater than the last year then what we want we want calculate and we want to calculate the sum of sales from the sales table so let me select this value over here and we want to filter it out using our year table so let me select my ear cola from the year table that should be equal to my last year so last year is the variable that we have created over here previously so let me select this one and then it should be the power of this one so we can multiply this so how we can do that we can use this and we'll say power of one plus okay so there is one underscore by mistake okay guys now this is our main formula for the forecast let me rewind it again for you first we created a variable last year which is the same that we created the previously then we have a variable number of years over here what we are getting we are first getting the value that a user is selecting or the context of your graph or the chart that we have this year value then according to that we are deducting the last year from that so that we can get this number of year differences over there and now in the file statement under the return statement what we have written over here if my selected value is greater than the last year for example i have my last value is 2017 and my selected value is 2015. so if it's greater than this then what should i write it but 2015 is not greater than 2017 so it won't do anything what if it is greater than this one so it's going to calculate my sum where i'm using the forecast formula sum of total sales for the last year that is the last represent over there and then it's multiplying and using this one 1 plus cgr then its power the number of years so this is pretty simple formula if i'll ask you but you should know the logic over there you should always get to know like what is the logic behind the forecast that you are going to calculate so you can use this as it is it's gonna definitely help you out but make sure you know the exact logic that you're gonna use now let me close this and let's bring the values so first of all we have to select a visualization so i'm gonna use this line chart this is the best representation which i can leave for this kind of time series analysis and here first i'm going to select my x's as my eos and then my value is my forecast here so let me bring it over here now let's see how it's running so i believe you have noticed one thing over here and then thing is that it's only showing me the forecast values it's forecast but yeah but what about my actual values where are my actual values we also need to see those values so now you can see that your forecast and sales values are there and there is a cap over here between 2017 to 2018. now how to eliminate this one so this is quite interesting isn't it but we can do that how over here in the forecast formula i'm saying when my selected value that means whatever year is over there on the x axis 2015 16 is greater than last year but i'm not saying greater than equals to so if i want to include this value 2007 then i have i have to say greater than or equal to and then let's see what happens so you will see a magic over there and actually it's not the magic it is the right thing to do because we don't want to make it any gap over there so that's how now you can see that these values are going over there and if i have to format it a bit i can do that so let me quickly format it for you so that it would look exactly the same that i showed you in the very first slide [Music] [Music] [Music] so guys now you can see that it's exactly looking like the same that i showed you previously and this is how we can calculate the forecast using tags this is pretty simple isn't it or are you still have any doubts so please watch this video again if you are still struggling calculating forecast using dax and if you have any further questions or concerns please reach out to our website which is www.biconsultingpro.com there you can find videos blogs vlogs and you can also post your queries under the section ask me anything if you would like to connect with us you can connect on our linkedin channels with us and there we post other blogs and updates so you can keep in touch you can get all the updates over there thank you very much for watching this video and please don't forget to subscribe our channel and hit the bell icon for the latest videos

Channel: BI Consulting Pro

Views: 33,696

Keywords: DAX Sundays, forecasting, forecast data using DAX in Power BI, DAX in Power BI, BI Consulting Pro, Power BI, dax tutorial power bi, dax tutorial beginner, Forecast financial data in Power BI, Ajay Kumar, dax interview, power bi tutorial for beginners, power bi interview questions, selectedvalue power bi, selectedvalue filter power bi, selectedvalue power pivot, FIRSTNONBLANK, LASTNONBLANK DAX, How to use DAX in power bi, power bi, power bi tutorial, Learn DAX, DAX with Ajay

Id: CAq02_Wf2sE

Length: 18min 20sec (1100 seconds)

Published: Wed Apr 21 2021