Welcome to Excel magic
trick number 1,408. Hey, if you want to download
this Excel workbook, Excel magic 1,408 start
file or the finished file, so you can follow along, click
on the link below the video. Hey, back at Excel
magic trick 1,406, we were trying to calculate
a moving 12 month average. Here’s our sales table. And here’s the formula we used. We used averageifs
and end of the month. Now below the comments,
Victor asked, hey, how do you do this
with a DAX formula? Now one thing
about this formula, this is averaging
the last 12 months given the end of January 2017. A lot of times, moving
or rolling averages are done with number of
months as the denominator. We’re doing this
average calculation with number of transactions
as the denominator. Now below the comments, someone
also posted this awesome link from Marco Russo
and Alberto Ferrari about how to do it by months. Now anytime we start
doing DAX formulas, we have to think about what
criteria is sitting in the row or column or filter or slicer. For us, we’re just going to have
year and month in the row area, because what we do is we
create a measure, a DAX formula, and the formula
will see the criteria coming from the pivot table row area. Now if I use the average
calculation here, it would just see all of
the dates for February. And it would know to calculate
the average for February. But somehow, we need our measure
to look at the input, which is the criteria
here for February, and somehow get it
to automatically look back from the end of February
2017 all the way back to March 1, 2016, get
those transactions and calculate the average. All right, we have
two tables here. And unlike the formula
version, we just worked off the actual fact table with
the sales numbers and dates, we’re going to have two
tables, our fact table and then our date table. Now our date table has all
the dates from 1/1/2016. Control down arrow
to 12/31/2019. Control, up arrow. Now if we look at our pivot
table, our calculations we want are just between the beginning
of 2017 all the way to 2018. But notice when
you drag something from the date table
like year and month, it’ll show everything. So that’ll cause
a little trouble, and we’ll see how to fix that. Now I’ve already added both of
these tables to the data model. So in the PowerPivot
ribbon tab, I’m going to click on
Manage data model. Here in diagram view, you can
see there’s the F sales table. There’s the date table. And we have a relationship
between the two date column. Let’s go over to data view. We’re on the sheet
with F sales table. Below the table, this is
called the measure grid. This is where we’re
going to create our DAX measure or our formula. As always, we’ll type
the name, colon, equal, and then the formula. Now as soon as I
start typing, M, it jumps me up to
the formula bar. So moving 12 month
average, colon, equal sign. And I would just like to use the
average function, F, and then down arrow, so I see F
sales, sales, column, Tab. We always have columns
with table name, field name in square brackets. Close parentheses. The problem with this
is this will totally see the filter context. So for February, it will give
me just February’s average. Well, there’s a great function
called calculate, C Tab, it can change the filter context. Now the expression is always the
formula you want to calculate, come to the end, comma. Filter, this is where we
add some conditions criteria on tables or columns to
change the filter context. Now remember, if we think
about February 2017, that filter context flows in. And then we have to
build a filter here that takes that
into consideration but then extends
it back 12 months. Well, there’s a great
function, dates. Well, we could
use dates between. But an even more succinct way to
do this, it’s dates in period. Now dates in period is one
of many time intelligence functions. And one of the reasons we
had to have a date table is because all the time
intelligence functions that we’re going to use
require that date table. Now the first argument
is dates, DD, down arrow, we’re going to put the
first column of the date table, comma. Then we need a start date. Well, what we’re going to use
as the start date is really the end date. We’ll give it whatever the last
day is in the current filter context, and then we’ll
go backwards a year using number of intervals
and the actual interval. And so for the interval,
we’ll say year. Number of intervals,
we’ll say minus one. So start date, I’m going to
use the last date function. Now for dates, I put
DD, down arrow, date. Filter context flows
in, so February 2017, it sees all the dates. It’ll automatically
pick out the last one. Now remember the dates flowing
in are from the date table. And it always has
all of the dates, including the end of the month. Now I’m going to
close parentheses. So that’s the start date, comma. We want to go back one year,
so we put minus one, comma. And look at that, we can choose
day, month, quarter, or year. I’m going to down
arrow to year and Tab. Now when I close parentheses
on dates in period, this whole function
will automatically generate all the
dates and supply them as a valid list of dates
for the date table. Those validate dates will
flow across the relationship into the fact sales table. So when average
calculates, it will only have the dates and the
corresponding sales numbers for exactly one year backwards. I’m going to come to the end,
close parentheses, and Enter. One of the many great
things about DAX formulas is we use them in a pivot
table, but we automatically get to add our number
formatting upfront when we create the formula. Alt Tab to jump back over to
our already started pivot table. There it is. There’s our formula. When I drag it down
here, wow, look at that. If I look down to
January, that’s the same number we got up here. February 2017, same number,
all the same numbers. Now we do want to
turn this off up here. We’re only interested
in 12 months. So these all have
partial months. Now this one actually does
have a valid 12 months, but we’re going to exclude
all of 2016 and 2019, which have no sales down here. These are just
looking backwards. But we want to
exclude ’19 and ’16. Really, we want to exclude years
that don’t have sales and then the first year. Alt Tab, now I’m going to come
up to the formula bar here and I’m going to build an if. Now there’s two conditions. And in fact, if we go back over
and look at our pivot tables, this is the first year. We want to exclude it. This is a year with no sales. We want to exclude it. But the logical test
we’re going to do is I’m going to say two
things have to be true. The year and the
current filter context has to be greater than 2016. And the sales for
this filter context right here has to be
greater than zero. So two conditions
have to be met. Back over in the data
model, right before the C, I’m going to type
the if function. Logical test, I’m going
to use the and function. Logical test one, I’m going
to say the sum of F sales. And as this is copied
down in the pivot table, always look at the
current filter context. When it gets into the year
’19, there will be no sales. So I’m going to say any time
the sales are greater than zero, then we want to go
ahead and calculate the average calculation. So that’s our first
logical test, comma. And the second
logical test, well, I need to pick out the year
in each filter context. So I’m going to use the max
function to get the biggest year in each filter context. DD, down arrow to year. Tab, close parentheses. Now I’m going to say any time
that is greater than 2016. So you can imagine max picking
out 2016 for the year 2016. So alas, the question,
is it greater than 2016? False. But everything
after will be true. Now that’s the second logical
test, close parentheses, and then comma. So both of those
conditions come out true, then we go ahead and calculate. Now the great thing about
the if over here in DAX is if you leave the
last argument out, it will automatically
use the blank function. Now blank is a substitute
for empty cell in Excel or nulls in a database. Close parentheses. Enter. Now when we Alt Tab,
2016 and ’19 disappear. Any time you have
a blank like that, then the row criteria disappear. And there’s our calculation. Now we could go one
step further here. Notice right here, we
hardcoded a value in. And that might be fine if
this data set is really always going to be 2016. But let’s just see if
we could substitute a formula in there that would
automatically pick out the min. So if we added 2015 sales,
the pivot table would update. Now I’m going to start this. I’m going to do it down
here and just look what happens in the pivot table. I’m going to say test min
here, colon, equals sign, and let’s just see what
happens if we do the min. DD down arrow to year,
Tab, close parentheses. Enter. Well, of course, just
like the max and the min, if we come over to our pivot
table and drag this here, it’ll see the filter context. So notice ’16, ’17, ’18,
that, in essence, is what the max function is doing also. But we really want this to
say 2016 all the way down. Well, how do we change
the filter context? We use the calculate function. So calculate. There’s the formula, comma. Remember what’s flowing in is
each particular month period. Well, what we want to do is
to remove all of the filters on the date table. And the way you remove filters
is with the all function. Now notice this says
table or column. We’re going to use a column. DD, and I’m going to
down arrow to year. Now when you use
all on a table, it exposes the entire table
removing all filters. When you use a single
column, not only does it remove the
filters on that column, but it actually delivers to the
formula a unique list of items. So for us, all is delivering
four years to the min. And then the min picks
out the min year. Now I’m going to
come to the end, close parentheses, and Enter. Alt Tab, and now we can see
we get 2016 all the way down. Now I’m going to copy
this, Control, C, Escape. Click back on the formula. And then right there, I’m
going to double click 2016. Control, V, and Enter. Now I’m going to come
down here and delete. It asks us if we want
to delete from model, I’m going to click that. Alt Tab, and there we go. Now let’s check out
something totally cool. Now if you watch Excel
Is Fun channel a lot, you know that I have a lot of
videos on huge gigantic array formulas. And I tend to build them
just like I did here from the inside out. So they make sense to me. But if you’re reading
this code straight out, it’s hard to read. So Marco Russo and
Alberto Ferrari have done something
amazing for us. If you copy your code, the
entire thing, label, colon, equal sign, and formula,
Control, C, and go to Google and search for DAX formatter. I just search for DAX formatter. And there it is. This is the coolest thing ever. We can click in
here, Control, V, and look at this
over here, format. And there it is. We can copy this,
Control, C, Alt Tab. And right where I have this
highlighted, Control, V. Now I still sort of like
the long formulas, but that is often
used convention. And that website just
makes it amazing. Now it still works when I
hit Enter and go back over to the pivot table. It works just the same. Even though over here, it
has lots of formatting. All right, that was a little
fun creating a DAX formula to calculate a moving 12
month average on daily sales. All right, we’ll
see you next video.