How to Calculate Moving Averages in Power BI - The Ultimate Guide

finding out a moving average is a pretty common ask in the business and we'll obviously see that how can you find moving averages in parbi but more importantly I'd also like to demonstrate that how do you validate the results and how do you build a check around moving averages so that the user is confident that he is taking a look at the right numbers all right no further Ado you're going to have fun let's start all right let's explore the idea for the moving average for just a quick second and then we will proceed on to build the calculation together here I have built a simple pivot table which is where the First Column is the year column the month column these two columns year in the month are coming from the calendar table and then against that I already have total sales calculated it's pretty simple I'm sure you can figure that out now for the total sales I would like to find a moving average for the past 3 months hypothetically speaking and then we will also automate that so for the month of January because we don't have any prior data to January the moving average is going to be the very number that we see for the month of February the moving average is going to be for 2 months because you know that's what the data is available to us so that's what the number is going to be and for the month of March you know from here onwards you will start to have 3 months of data and you were going to have 3 months moving average when you move to the April month you're going to have moving average like this and then like this and then like this so on and so forth well you get the idea now what we're going to do is first of all we will create a dynamic period which is going to go take a look at 3 months back dates and then against those dates we will try to find the moving average so let's just start creating our measure in the sales table so right click make a new measure I'm going to call this as moving average and I'm going to start to use a variable and I'm going to call this as my period now the function that I'd like to use is something like dates in Period which is a function that can conveniently give us the moving dates in the past 3 months and it asks me hey what's your calendar date column so the calendar date column colum is right here that's my first one then it says hey what's your start date so my start date is going to be the end of the month so if my filter context is the month of April then my start date becomes let's say the 30th of April right if my filter context is the month of May then my date becomes the end of May and for that I'm going to use a function called Max of the calendar date whatever context you are in at the moment just pick up the last date in that context then it says how many periods would you like to move move so I'd like to move -3 periods behind and then what's your interval I'd like to move by the month that's it close the bracket now dates and period actually is going to give us a table and returning this in the measure is going to give us an error because you can't really return a table in the measure well what we're going to do is at least validate our results so I'm just going to say hey why don't you count the number of the trows in the table that we have created which is nothing but the period table and let's just see what the result is going to be so moving average dragged right here now in the month of Jan since we are only looking at the month of Jan and there is no prior data that's 31 days that's 59 days considering Feb is shorter month and then 90 days 89 days 92 days so on and so forth seems Seems right now what I'm going to do is I'm going to start to build the formula further and I'm going to say something like hey you know what for all of these dates that we have received which is the rolling dates of 3 months I would now want to calculate total sales because moving average calculation has two parts it has the numerator as what's your total sales and the denominator is going to be the number of months so what I'm going to do is I'm going to create another variable so I'll save bar I'm going to calculate total sales which is going to be something like calculate hey I would like to calculate my total sales and here is going to be the period filter for whatever period that I'm trying to calculate and let's just try to return our total sales numbers right here where are you right here press enter let's just see what do we get now for the month of Jan we are getting the very number that's good for the month of Feb we are getting the total of these two that is also good for the month of March the total of the first three the for the month of April the total of Feb March and April and that also seems to be right now we have received the numerator we've also got to have the denominator and the denominator is got to be the number of months right so let's just go validate that real quick so I'm just going to create another month right here so I'm just going to say VAR uh this is my months and that is going to be let's say the unique count or the distinct count of the calendar table month column so calendar table month column so just make a unique count of that given that the filter is the period filter that we have just applied all right let's just go validate the results quickly for this month press enter what do we get we have one month here so whatever numerator that we had divided by one Whatever numerator that we had divided by 2 divided by 3 so on and so forth and that is going to fetch us the right result now the only thing remaining is to divide the two numbers so I am going to say hey I'd like to divide my total sales which is right here divided by the period which where are you right here close the bracket and commit to this press enter and this gives us an error because this should not be divided by the period I'm so sorry it should be divided by the month and press enter and that gives us the Right Moving average once we have calculated the moving average the next thing that we would want is to give the user the confidence that the calculation is right and is giving you the number as it is expected now once you take a look at any number perhaps let's say this number 11:30 now the user will literally have to take a total of the three numbers right here divided by three to get to this number that means the validation resides in the hands of the user he will have to do some math maybe on the back of the envelope to get this can we help the user build another calculation that that is going to act like a check as to what we have built is the right answer and that is just going to make the model better right so what we're going to do is we're going to write another calculation so a new measure and the measure is going to display that what numbers are being shown up in the calculation so I'm just going to say that this is nothing but my moving average check and this is going to be pretty much like the measure that we have created the only difference is that rather than adding the numbers out we will display the numbers what numbers are leading up to that calculation and you'll see the result so I'm going to create a VAR here and I'm going to say that this is nothing but my period or just call this as months let's say and I'm going to say something like hey you know what I'm trying to summarize a table in the table I'd like to have three months of data which is the rolling months and to be able to get that we need the dates and period function that we use so dates in Period which is going to be hey the calendar date obviously so calendar date then it says hey what's your start date which is where we said the max of the calendar date so that is the max of the calendar date and we asked it to move behind 3 months so uh move behind 3 months and then uh that's going to be the month now that is nothing but a filter that is going to move the dates behind 3 months but I don't really want the dates I don't want one genan 2 genan third genan so on and so forth I want Jan Feb and March that's it that's all that I would want so I am going to use the dates in period in my summarized function to build a filter for that so I'm going to say something like hey I would like to calculate a table and the table that I'd like to calculate is summarize of let's say the calendar table and the column that I would want in the calendar table is going to be let's say the month the month index and of course the year in the calendar so the calendar table is going to look something like this right you will have a column called ear in the calendar table table you will have a column called month in the calendar table and obviously the month is sorted by the month index to display the months in the right order so these are the three columns that you're going to get in the calendar table no dates just the months now this particular table is going to be made for only this particular rolling period that we have created right here now what we're going to do is we going to quickly test this calculation out real fast so I'm just going to go right here and I am just going to close the bracket real quick and I'm going to return the answer and I'll say hey why don't you count the number of rows or in fact let me just use the concatenate X function so I will show you what the table looks like so concatenate X here is a table that we have created which is the month's table so go inside every single row of this table and why don't you pick up what is there so I'm going to say hey there is a year column that we have created please concatenate that with let's say a little Dash or maybe a pipe and then also show me the month column so month I hope we get that month and that's good to go so close the bracket press enter and we'll drag this calculation out in our Visual and you're going to see that we have been able to get the year and the month so this is 2011 Jan that's good and then we have 2011 uh Jan and 2011 Feb let me just kind of put a comma so that this becomes absolutely clear where are you moving aage check right here and I will say hey why don't you just delimit yourself with a comma which is a part in concatenate X now if you're not aware of how to use concatenate X obviously I will suggest that you watch another video of mine and I have done some excellent examples of concatenate X that you're going to find it very very helpful nevertheless let's continue so concatenate X Loop through this table and please concatenate the year and concatenate the month and then put a separator as a comma in between take a look at the result what we have been able to get is here there is just one month here there is 2 months of data here there is 3 months of data here here there is four again three months of data but rolling so on and so forth we have been able to get the months now what we going to do is against every single month we will fetch the value that is being added and displayed right here it's going to be fun so what we going to do is something like this I'm going to say that hey go inside every single row of this table but I don't really want you to concatenate the year and the month that's useless I would want you to concatenate my total sales calculation and let me just format this a bit so format total sales and I just want to have let's say no decimal comma separated value displayed right here and that I believe is good to go and let's say the Del limiter is going to be a common all right now what we get is nothing but the values displayed right here so this is 1199 and then the second value the second value so on and so forth and this is just working out beautifully now it actually tells us that these are the three values which are feeding into this particular number obviously if you have built the numerator you would obviously you like to build the denominator too so I am going to go ahead and say something like rather than having comma why don't we add a plus sign and this looks much much better let's just also add some commas in here that looks good and then what we are also going to do is we going to add a bit of brackets so that it actually looks like a calculation so I'm going to say hey why don't we start with an Open Bracket concatenate that with this calculation and Plus+ all of that and then end this calculation with closed bracket and that is again going to be an n% so let's just see how this calculation turns out to be press enter and that is my brackets added now I would like to divide this by another calculation which is nothing but the number of rows in the table that we have created which is nothing but the months so I'll say count rows of the months table so months and close the bracket press enter and that is divided by three and that is divided by 1 that is divided by 2 divided by 3 divid 3 and this is a check that we have built that the numbers that are leading up to the calculation are these three numbers and divided by 3 it gives the user a lot of confidence to understand that how the calculation was built all right final part of the video that how are we going to transform this into a more Dynamic calculation at the moment we are necessarily going 3 months behind what if I'd like to have 4 months behind moving average or 6 months behind moving average I want that control to be in the hands of the user so what am I going to do I am going to create a table for creating a slicer so I will go ahead in the table tools and new table and I'm going to create a table which is going to give me the ability to make a slicer so I will say selected months which is going to be let's say five or six values whatever that may be so generate series is the function that I use start with one end with let's say 12 months behind close the bracket press enter and that's what we get on this value column we're going to build a slicer so I'm going to come right here and let's just add a visual and the visual is going to be a Slicer in the slicer I will add the value column from the table that we have just created that is what we get so now I'm just going to maybe put this right here and then at the moment if I click on the slicer nothing really happens but what I'd like to happen is that this selection should affect the calculation this one and this one as well so why don't we actually go ahead and create a new measure and I'm going to say this is my selected month which is going to be selected value of the value column that means what value is picked up so five is picked up so that goes right here and in case the user picks up two values or picks up no values then in that case I at least want to go 3 months behind that's my default value or my alternate result press enter and this kind of let's just validate the result real quick this gives me five six if I pick up two values or if I pick up none values it actually gives me three that's good now this five is going to feed it into our calculation so I'm just going to open up the moving average calculation and this -3 is going to be my measure that we have created which is selected month press enter that's good and this moving average uh which is where I have -3 is also going to be my selected month and that is good again now 5 months of data pretty good 1 month 2 months 3 months 4 months 5 months this looks fantastic now if you were to convert this into a chart it look something like this go right here and convert this into a line chart and in the line chart so year and the month are there on the horizontal axis that's nice total sales and moving average are there on the y- axis which are my lines which is also nice and the moving average which is the check that we have built is there in my tool tip so now if the user happens to hover the mouse on any particular month not only is he going to see the sales and the moving average but also the moving moving average checks that tells you that what numbers are feeding into the calculation that is building up the answer and it looks it looks pretty good I mean it works it just it's it's nice all right that's been it on the moving averages if you have any questions obviously I will recommend that you please post a comment and I'll be glad to reply in the end before you go I'd like to give a big shout out about my courses on parbi especially Dax power query and the M language courses it's going to be highly beneficial if you're trying to start out your journey with parbi and you stumble on hard problems you're not able to get your way around that you'd like to understand the logic build up your fundamentals and then start to solve even harder problems of your own data I'd highly recommend that You' please take a look at my courses it's going to be super awesome thanks so much for sticking all around and I'm going to catch you guys in the next video bye [Music] now [Music] no

Channel: Goodly

Views: 13,597

Keywords: Goodly, Chandeep Chhabra, moving average, data visualization, business intelligence, Power BI tutorial, Power BI tips, Power BI tricks, Power BI calculation, moving average calculation, validate moving averages, build dynamic period, automate calculation, check calculation, Power BI model

Id: FLjygam5x-I

Length: 16min 4sec (964 seconds)

Published: Wed Dec 06 2023