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.