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.

This moving/rolling annual concept is a powerful and standard metric that can be extended to data at different grains (i.e. rolling annual week or month). Also I highly recommend daxformatter.com to augment your measures into more legible, easy-to-follow code.

Like the DAX formatter, however I'm with you. I like formulas in groups of functions better and "if, then, else" breaks. Something like:

M12MA:=IF (AND (SUM(fSales[Sales] )>0, MAX(dDate[Year] )>CALCULATE(MIN(dDate[Year] ),ALL(dDate[Year] ))),

CALCULATE (AVERAGE ( fSales[Sales] ), DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1,YEAR)),

BLANK())

But that's just me. I assume after time, the DAX Formatter way would be easy to read, esp. for BHAF (Big Harry Audacious Formula!) as well… Love the use of "DatesInPeriod" too. Thanks for sharing…

As always no comment

we hope to see you live on the next dueling and also the master bill jelen

Mike, Is there no F9 Key to evaluate in DAX? Can we use excel given functions to calculate i.e. without using DAX? Your videos are awesome.

Mike, your videos are amazing! Thank you so much for all your time invested in them!

Mike, could you please clarify more on "CALCULATE" function in filter context terms on some basic data. Really not getting this concept, need your guidance. Thank you.

Again a great Video Mike, thanks a lot! One thing is to read the book or to see the formulas – something totally different is to see your video and listen to it, with all additional tipps and tricks -> Really unique !!! I can also recommend the DAX Studio AddIn for Excel, is very helpful for DAX interested users. Thanks and Thumbs UP !!

Hi Mike,

As usual, extremely informative and so stimulating to test things on our own! 😉

Thank you for your hard work!!!

Hi Mike,

Long time viewer of your incredible videos, first time commenting!

I was wondering if we can use the TODAY() function rather than the LASTDATE function in the formula you were using. If we can't use the TODAY() function, why not?

Thanks Mike!

Evan

🙂 🙂

Hey Mike! Awesome explanation. I'm beginner in Excel. Which video to start learning Data analytics?

would you please help me on this, how to connect two different workbooks in smart, intelligent and efficient way ?!

Hi Mike, Thank you for all your wonderful videos on here. Could you please tell me how to do the M12M for a specific month or most current month with sales for a year backward. For example: say I want M12M from April 2017 rolling back to April 2016? And when I update my data for May 2017 , it should rollback to May 2016.

Regards,

Olan.

Fantastic – I've been looking for a rolling 12 month, monthly calculation. This looks like the ticket all I need do is change the average to sum. Now here is a question, can this be done in power query using M Language. Not the right area, but some in the company do not have Power BI or the higher version of excel

Thanks for the video. It was extremely helpful and I wanted to know how do you account for months when there are missing values. If a month has a missing value the formula returns a blank.

Great video, Mike! What if you want to know the 12 month moving average of how much sales you bring to the business on a monthly basis based on daily sales? You would need to sum the monthly sales first and then apply the 12 month MA, right? How would you do that with DAX?

Excellent video! Thank you.

very nice explanation

How did i miss this??

Dear Mike,

The link below refers to rolling 12 month average posted by Ferrari. The scenario is quite similar to your video, only the slight difference is that the example demonstrates 12 month moving on monthly sales, not daily sales. the hardest part I cannot understand is calculation of Months12M (in the link below just please roll down to see this part) which is used to calculate the number of months for which there are sales over the past 12 months.

Months12M := CALCULATE (

CALCULATE ( COUNTROWS ( VALUES ( Calendar[MonthName] ) ), Sales ),

DATESBETWEEN (

Calendar[FullDate],

NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( Calendar[FullDate] ) ) ),

LASTDATE ( Calendar[FullDate] )

)

)

I really don't understand the behavior of this measure, though it generates the correct answer. If you don't mind could you please help me to explain the above measure step by step? I cannot find resemble answer in the internet and your video is the most similar to my case. here is the link:

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Thanks a lot.