Welcome to Excel Magic

Trick number 1,407. Hey, if you want to

download this Excel workbook Excel Magic Trick 1,407

finished file or the start file so you can follow along, click

on the link below the video. Hey, back in Excel

Magic Trick 1,405, we saw how to create a monthly

revenue and add cost report, but the sales numbers were from

a table that had daily sales, and the ad costs were from a

table that had end-of-the-month amounts. Well, for formulas, it was

easy, because we could point our formula to the right table. Notice, there’s the

criteria, the formula’s pointing to the sales table. Over here for ad costs,

there’s the criteria, but we’re allowed to simply

point to a different table to summarize. Now in the comments section

below 1,405, Matt asks, how do we do the same report

but in a data model pivot table using DAX measures? Now the trick for a pivot

table and using the data model is that these two

formulas are going to be looking at this

condition or criteria. These two formulas will see

all of the dates in January– January 1, 2017 all the

way to January 31, 2017. But both formulas will be

looking at the same table, whereas over with

formulas, we were allowed to just point the

formulas to different tables. The point is, both

these formulas need to see that

criterion somehow calculate the correct number. No problem, it’s actually going

to be pretty straightforward. We just have to

create a date table, and then from the sales table,

connect through a relationship the date over to the first

column in our date table with a unique list

of dates, and then do the same for end of the

month for our cost table, build a relationship

between end of month and the first column

in our date column. Then it’s as simple as dragging

the month and year into the row area and summarizing

with a simple SUM function, ad costs and sales. Now I’m going to close

this finished workbook and we’re going to go

over to the start file. So the key for us is going to

be building this Date table and then importing

all three tables, creating the relationship,

and then using a simple SUM. All right, it’s easy to

create a Date table in Excel. I’ve already put the

field names at the top, and there’s the first date. Now I had to look through

each one of these data sets and make sure I have the

earliest date possible, and for a date table,

you have to have a complete list of

every single date from whatever the

min to max would be in each one of your tables. All right, so I put 1/1/2017. Now watch this, I want to

take my selection cursor and point to that little

green fill handle, and when I see my

selection cursor turn to a crosshair

or angry rabbit, I’m going to right-click. So I’m right-clicking, I’m going

to drag down one cell, then back up, and let go

of to right-click and up pops a secret menu. I point to Series. Now I want to fill this

series of contiguous dates down a column, so I select

Column, Step value one day at a time, and check this

out, there’s the stop value. I simply put 12/31/2018,

and when I click OK, boom, there’s all of the dates. Now we’re going to

need month number, and we’ll see why

in just a moment. Month. That looks at a serial number

date and we’ll report 1 to 12, Control-Enter. Actually, I should have hit

Tab, I’m going to hit Tab. Now we’re going to need

the name of the month. So in Excel, we use TEXT. It looks at a value– remember,

all dates have serial number values underneath, comma. And then we have to know

the custom number format for showing the month name. Well luckily, of all the

custom number formatting, dates are the easiest. M is for month, D is for

day, and y is for year. So if we want to see a

three-letter abbreviation, we simply in double quotes put

three m’s, close parentheses, and now I’m going to hit Tab. The year equals YEAR function. It looks at that serial

number date, Control-Enter. Now I’m going to highlight all

three formulas and double-click and send it down. Go to the last cell,

Control-Down Arrow, F2, Shift-Tab, F2,

Shift-Tab, I’m checking to see if each formula is

working, it’s looking good. Control-Up Arrow. Now, we have to convert

this to an Excel table to get it into the data model. So I go up– with one cell in

the table selected, and I go up to Insert Table

or simply use the keyboard Control-T and Enter. Now I need to get each one

of these into the data model, so I go to Power Pivot,

and with one cell selected, I click Add to Data Model. I could see, there’s the fSales. Alt-Tab. This is the fAdCosts. Click in one cell,

Add to Data model. There’s are two tables. Alt-Tab, click in one

cell on the Date table. Ooh, can’t believe

I almost forgot to do this, the most

important thing when you’re using Excel tables– Design, Properties, that

is a terrible table name. You always want

to name your table because the name of the table

is used in formulas over here in Excel and over

in the data model. So I’m going to call

this dDate and Enter. Now I have a single

cell selected. Power Pivot, Add to Data Model. I have my three tables. Now I go over to the Home

ribbon, View, Diagram View. I see my three tables,

and look at this– we’re going to have

to fact tables, and there is one date table. Now to create a relationship,

I simply drag– and remember, date has a unique list

in the first column here. I’m going to drag it over

to the end of the month. Now there’s a

one-to-many relationship, there there’s exactly

one of each date here, but over here we have multiple

end-of-the-month dates. That allows us when

we filter this table to have the filter go

through the relationship and filter down just to the

actual Ad Cost amounts we want. Now we do the same thing

for our second fact table– so I’m going to click on Date

and drag it over to Date. This is a one-to-many, there’s

a unique list of dates here. One day may have potentially

many sales over here. When we filter this table– for example, when we

filter on January 2017, all of those dates will

pass through here– well there’s only one date here, so

the end of the month of January will show, but when

the filter gets passed through this relationship,

all the days– and we potentially have

sales on all of those days, but this table will be filtered

down just to sales for January. All right, now we need

to go back to Data View. And I’m going to choose to make

our measures on the fSales, so I click on fSales,

drag up the divider between the table and then

what’s called the measure grid. We click on the

measure grid, and we can create our DAX measure. Now we simply start typing

the name of the measure. Total, I immediately get

shot up to the formula bar, Total Revenue, and we have

to use a colon and then an equal sign, and then we

put whatever formula we want. We want a simple SUM. So I type SUM, open

parentheses, F-S to get to the fSales, and

then down arrow to Sales. Close parentheses. So when you have a DAX

measure, everything before the colon

and equal sign will be the name that shows

up in our field list; everything after the colon

and equal sign is our formula; and whenever we have

columns, we have to put a table name and then in

square brackets the field name. Enter. Now here’s a great thing

about DAX measures. I can add number

formatting to this formula. I’m going to click on

English United States. That number formatting will

follow this formula around now let’s do the

same thing for Total, it shoots me up here, Ad Cost,

colon, equal sign, SUM, type F, and I immediately see from the

first item in my list Ad Costs table and then in square

brackets our AdCost. Close parentheses, Enter. Add some number formatting. Now we’re done

over here, and you can come back over the Diagram

view and see sure enough, the measures are

added to this table. We could have just as easily

put them down there below. We could have put total ads

up there, total sales here, but I’ve put them both

in the same place. In fact, just to make sure that

we don’t accidentally drag Date from here and we only want

our dates from the Date table, watch this. I’m going to click

on the first date, hold Control, click on

Sales, because I don’t want anybody dragging

sales either, I only want to use my measures. Right-click, Hide

from client tools. Over here in the

Ad Cost, I’m going to click on AdCostID, Control

and click on End of the Month, right-click, Hide

from client tools. Not only that, but we

don’t need month number we’ll see why we need it,

but we don’t need it over in our field lists,

so right-click, Hide from client tool. Now let’s Alt-Tab. I’m back in Excel, I want

to create a new pivot table from the data model. So I click in the cell

I want the pivot table to go in, go up to

Insert, Pivot Table, or use the keyboard Alt-N, V.

Because we have a data model, it assumes we want to

use it as our source. It already has the correct

cell right there, so I click OK or simply hit Enter. And I’m going to scroll

over, and check this out. If we look at each

one of our tables, we only have the

fields that we want. Now we want Year. So I’m going to drag

Year down to Rows. Then we want Month, and we’re

going to run into trouble here when I drag Month down to Rows. Uh oh, it’s sorting

alphabetically. No problem. Alt-Tab, over to Data View. And I need to go down to

dDate, so I’m in dDate, and I need to tell this Month

Name column to please sort by a different column,

and that different column will be Month Number. So the reason we had

Month Number here, and so now January will

be associated with 1 and it will appear first

in our sorted lists. So when I click OK– now April, of course, is 4, so

when I Alt-Tab, there we go, April’s in the fourth position. I don’t like this layout,

so I go over to design. Layout, Report Layout,

Show in Tabular. Now this is the magic–

we have our two formulas, Total Revenue. I drag down to Values. Look at that, including the

number formatting, and Total Ad Costs, boom, it’s working

just like our formulas. Now each one of these measures,

remember, those are formulas. They’re actually looking

at a filtered date table showing only January. Here, it’s only February. So if we go back over

to the data model, Diagram View, in that

pivot table cell, this table behind the scenes

in the column to our database is filtered down

just to January. That passes through

here, and since 1/31/2017 are the only dates

here, this table is filtered down just

to those three costs. Here, all the dates

for January 2017 are passed, that

filters this table. Now we don’t have a big data

set, and that is fundamentally the reason that this data model,

DAX measures, and relationships calculate so

quickly on big data, because when the tables are

filtered down to a smaller data set, faster calculating. Alt-Tab, there you go–

how to do revenue and ad costs from two different

tables, but with the data model, pivot table, DAX

measures, and relationships. All right, we’ll

see you next video.