Advanced DAX - Rolling Average

Parker Stevens
A free video tutorial from Parker Stevens
SQL/BI Developer
4.5 instructor rating • 1 course • 37,905 students

Learn more from the full course

Learn Power BI Fast

Build a professional Power BI report in less than 2 hours. Learn data visualization basics and advanced techniques.

01:53:42 of on-demand video • Updated February 2020

  • Build a Power BI report from scratch
  • Create calculated columns and measures to create a more robust data model
  • Use advanced DAX in order to perform complex calculations
  • Style the report to make it beautiful and fun to use
  • Push the report to Power BI Service and configure automatic data refreshes
  • Download and install Power BI Desktop and SQL Server 2017
English [Auto] So let's create some advanced index queries. First thing I want to talk to you about is the calculate function the color the function is possibly the most used function in the docs language. And basically it says calculate this aggregation meeting some average or count under these filters are given these filters. And let me show you how that works. What's the new measure. We're going to call this some if male and and zero children. The reason I'm making this so specific is because we can see below in our bar graph that we know exactly how much males with zero children have bought. So it'll be easy to test that our query is working. So we're going to start by typing calculate Schiffner to go down. And basically what we're saying here is some of sales amount and then you put comma. And now this is your filter. So only some if the deny customer Jinder equals quote. And also you can put in my filters as you want. If children number of children at home it was zero. Go ahead and end off. Your prince could enter to see if this is working properly and click out of it. And we're going to put in a new. We're going to put in a new card this up here put in our new some mail and your children. Eight point nine million. And we'll have you here. Hey what do you know. Eight point nine. So you can see how this measure was able to calculate properly what has already been calculated but it's just a good way to introduce you into the calculate function. One thing to note is that even if you click female and now these male calculations in a way it's still eight point nine million because in our query we wrote the half male and zero children so we're putting those filters and those filters are used regardless of whatever other filters are here. So that's just an intro into the touch of that function. But let's get you into a very very advanced function that you might use in your job. What if you're working with cells. And that is rolling averages. So imagine we have sales data in this line chart here. That's every week we have a certain sales amount but we want to see how are we coded compared to the last four weeks average. So let's go ahead and build that let's get rid of this car here. And let's go ahead and get rid of this right here. We want to create a new measure we call this four week average and we're set that equal to calculate again. And we're going to see some cells not again as all that. And now for this filter it's pretty advanced. We're going to want to some the sales now for every day from this date going back 28 days 28 days represents four weeks. So the way we do that is a very handy function called dates in period and it returns the data from the given period. So every day from today which is just a week date the way you should think about it is as this measure is going to scan through your table. It is going row by row. And look at the week date of the row it's on and uses that date. So everyday from that leak date to last date which is another function to return the last non-blank date of the week date. But going back to eight days negative 28. And you have to specify the unit which is day that was a lot of goes off of those a lot. Now I understand that that might take a little while to look at. Let's go through one more time. So using calculate we're saying some if given this filter. So we're seeing the cells now from the period of today this current date to the date 28 days ago. So using this filter give me the some of the cells now. And since it's an average We want to divide it by the number of days the number of weeks in in the calculation. Can you explain but what we just did is we sum all the sales up. Now when you divide by 4 to get the actual average so divide by. And we're going to take the almost the exact same thing here. Let's just type it out for good measure calculate instead of some We're going to want to take the distinct count of the week date for the exact same philtre I hope that makes sense. I'm taking the count of the week dates in this 28 day period and you can think of that as you think of that as let's say you're in February 2nd of 2014. It's going to have February 2nd January 26th January 19th and January 12th. Meaning it it's only going to have four distinct week dates. So what's clicking. Hopefully that makes sense. But let's just see how it looks on the visualization. Take this four week average and throw it on to this graph. Hey there you go. You have a much more smooth line because every week is very erratic and how the sales went. But the four week average is always is always a little smoother and you can see how this looks here. What do the exact same thing with a 26 week average meaning just six months of data and a one year average. That's also been very important to me in my current job. What we can do is click on for we gathered and let's just grab all this stuff and let's create a new measure 26 week average. If you can imagine your head will be even more smoothed out because it takes an entire six months of data into account. So 26 weeks. There are a hundred and eighty two days in 26 weeks. In case you didn't know that at the top of your head I didn't. I had to look it up. So the exact same thing that we just did but now we're looking at a 182 day period. Click Enter go this on the chart and there's an even smoother line you can see that we're below the six month average or we're doing better than six month average here that we start to do worse in them. Now we're doing better. Let's do the exact same thing for the 52 week average. Like reichlich you measure paste what we had already copied. Change this the 52 weeks which is one year ago 365 days back in both places. Click enter and less drag that on there. So Reason Code weve already written were able to create a four week average. At 26 we gathered and the 52 week average this is extremely powerful. And this also will filter based on the slicers selections. Do note that the simps the calculations are running in the background and we have three distinct calculations it might take a while to filter now but its very worth it. When you are able to see these averages like this supercool So there was actually an advanced daks. Now we will take you into another advanced calculation that you might have to use in your job.