 Hello everyone and welcome back to another video. So in this video, we're going to be looking at again focused on a power bi How we can create a rolling seven-day average from a totals columns that we have So this doesn't have to be just a seven day It could be 28 days 30 days or any duration of your preference I've just picked seven days as that was the previous or recent example that I had to use to build this calculation But as I say it's completely dynamic so you can put any days in there that meets your requirements So the first thing I'm going to do is just enter some data into power bi and the easiest and quickest way to do it Just to show you is as you can see I've currently got an Excel file here where I've got a load of dates For like the net for about five or six months worth and some random numbers I've generated in column B for the amount you can obviously just connect to this Excel file and pull it in But an even quicker way of doing that is I literally just select all this information and copy Come into power bi and go enter data And then you can simply just paste it straight into your table and we'll call it example table And there we go. We've got some data that we can now work with There could be a number of scenarios where you might want to do this if you've got a particularly a Static table you want to use within your your structure But alternatively if you're just trying to use or just try and do some demos or really quick Like following along with this video That is just a really quick way that you can get some data in to start using in the tool So if we have a look what we've got in there We just go into a table and bring in the date and the amount and we can see that's how it's looking in here at the moment and then while always Bring that down there and then what also do is rather than have this date hierarchy We'll just change it back to a date which we can do just by selecting date from there And there we go. We've got a table that we can work with so if we had to put this into a chart at the moment So let's go for Let's go for one of these an area chart. Actually, you know, let's get rid of that And we'll enter it in somewhere over here And let's say we'll bring our date into there and our amount down the bottom into values And again change that back to date. So we're working them all on the same pace And we can see it's obviously looking really quite messy there at the moment What we can do is if we just filter on this date and maybe we do a relative time Is that the right one relative date? Sorry, and we'll do in the last and let's say in the last 60 days So you've got two months worth of data there. It just gives us, you know, we can see a bit more on the page Should we say so let's just do that like that So at the moment, obviously with our amount obviously there's a lot of movement all over the place as the numbers are changing every day So this is why we want to bring in a rolling average So obviously it kind of smooths off our data But at least it gives us a trend that we can be obviously using in our graph So in order to do this what we need to do is create a measure So we're simply going to go to our table and go New measure and if you didn't see that when you hover over the table, you'll get these three icons here Or even over one of the fields. You'll also get this option to do new measure as well So once that's created you can see we've got our formula bar now ready to go and we can now start typing in our Calculation so the first thing we're going to do obviously is call it rolling average Well, if I'm actually selected doing in there rolling average Equals and then we're going to go on to a new line and for any of those not aware All you need to do is hold down the old button before hitting enter and obviously that allow you to enter new rows If you didn't hold down the old button and hit enter it's going to take you out of this formula bar Very first and we'll do and we're also going to be using variables for this You don't have to but one I think it just makes it a bit simpler and Anywhere where you can simplify something especially with measures is definitely going to get a tick by me So the first variable we want to the vine is our number of days So simply put we're going to put the AR Num days and for us that is going to equal seven so this is the number of days we want to use in our rolling average and Next part we're going to do is the second final variable We're going to be AR and do rolling some and then this is going to equal and then once again We're going to go on to a new line and it's going to tap in here And we're going to be using the function of calculate and you'll see there's a number of Functions that we're going to be using in this but I'll try and step through them and make it as simple as possible as I go through So the first part of our calculate sum is we want to get in our date So this is going to allow us to say we only want to do a total between this given date range And as you'll see with this structure that the first part we have to do is obviously define What our date range is so for us it's going to be obviously to today's day or the current date and Minus seven days give a rolling seven day period and the first thing we're going to do is obviously get a sum of all of those Amounts so if we were to look at the 7th of July on the screen here with five five zero We would want to obviously take that value and the previous seven days and add them all together So the first thing we'll do is get that total and then simply after that all we they need to do is divide it by Define and our suitable number of days So the first thing I'm going to do is get a sum of our amount. So we're going to type into a mount Well, and I can't actually see what I'm doing there So if we go in here to calculate and go into a new line Sorry didn't do that part today calculate some and we're typing here amount and then once we've done that We can then do close brackets another comma and go on to a new line And I will tie this up in a second and this next part we want dates Period so this is where we're now defining that date range So also the first part up here and sorry you've got this in the way But we'll go over this in a bit more detail in a second So the first part is obviously we want to do a sum of all the amounts and we're now defining What is the range of which to sum those values? So we're going to take in here our date. So for us that's example table date And then we're going to do a comma and then we're going to go last Date so it's going to pick up now the last day in that range So for us that is going to be again date. So example table date And then we can do close brackets because we've already then obviously captured what our last day is going to be The next thing we want to do is obviously define how many days we want to go back So we've already got this defined. So we've got our number days variable up here What's currently equals seven all we're going to do is just add a minus sum in front of that and then type Num days and the reason for doing that is obviously we want to go seven days in the past rather than seven days forward because we've each That will best example being once we get to today's date. We haven't got any future values We always want to go based on the historic numbers that we have And the last thing we're going to do in this dates in period function is we want day So we're just now telling our function that the the value of seven refers to the number of days rather than mumps quarters Or years so we can simply put day there Close brackets on a new line again. Just close our function off together and hit new line And then because we've done these as variables we need to make sure we're using obviously the return So we go return and then this is where we state what we want to be returned So it's not up. We will ignore the number of days and simply say we want to return rolling some and hit enter and Obviously at first glance it looks like it's done nothing But I'm now going to drag that newly created measure. We've got on the right hand side here into our table and There you go. You can see we've now got a rolling seven day total of our amounts column So if you go to the seventh of July, we can see and obviously you boil means get a calculator out this point But as our dates go on you can see it's gradually adding all of our amounts together to give that rolling total on the right here So start off with the first of July. Obviously, there's no historic information All it can do is take the value of the first of July and similarly for the second of July Obviously, it's only adding these two values But soon as we then do have seven days worth of data, you can see that seven day total is available to us So the last part we then need to do is to now give that or turn that total into an average And that's simply done by taking our total and dividing it by seven So if we go back into our rolling average sum So I just think through and get back into it So the next thing you want to do is rather than have rolling set or return rolling some here Take our rolling sum and then simply divide that by a number of days for us for us is Num days our top variable got the top here and we could get rid of these rows You know just to tie it up a bit hit enter and it will think about it hit enter again And it's now worked on it and there we go. We can now see we've got that rolling seven day average So we what we could do with that is with our graph we had over the side here And what we could do actually is we'll just copy this graph just so you can see the difference here And we'll bring that down below and this rather than having our mount remove that and we'll bring our rolling average into values And you can see obviously it's given us quite a different look and chart But by having our rolling average we can really sort of see where those peaks and troughs are and obviously It's given our data a bit of more of a smooth feel and if we maybe remove that day all together Yeah, we can see that's a bigger or better picture should we say if we open it up. So let's get rid of that one as well So rather than having this mess of showing our everyday values that was going off the amount column by using the rolling seven day average You can see our data is a lot more smooth and it gives us a bit more meaningful chart to work off So not that we need to focus on these charts too much. That's not the point of the video I just wanted to give you an example of where it's beneficial to use that rolling average and For those who have a different period other than seven or if you suddenly change your mind And you no longer want seven or you now need to do is go into your num days and change that to say I know we'll go to the extremes and go to 28 hit enter and You can see how our rolling average has now changed again So it's probably gonna give a oh it'll give a slightly different picture But you can see how the values are now updated and changed in our rolling average column So if you did enjoy that video, please don't forget to give the video a like Not only greatly appreciated by me but helps that all important YouTube algorithm And if this is the first time that you found one of our videos or you've seen them in the past Please don't forget to subscribe to the channel and hit that Bell notification button as well That way you'll be notified of all of our future videos as they come out You'll also see we've got a playlist section where we cover off things like power bi as we've done in this video here And we've also got videos and playlists for VBA and other functions available in Excel So be sure to also check those out as well. And lastly, thank you very much for watching and we'll see you in the next video