[MUSIC PLAYING]
Welcome to MSPTDA video number 30.
Yes, Microsoft Power Tools for Data Analysis.
And in this video, we got to see how
to deal with a fact table that has two date columns.
Yes, we have an order date and a ship date,
and we need to build reports based on each date.
Now last video we used Excel worksheet formulas.
This video we want to see how to do it with data
modeling and DAX formulas.
Now for the Excel example, in this video
there is the file you want to download,
and for the PowerBI Desktop example, here's the file name.
Here's a picture of our fact table.
We have order and ship date, and there's the sales column,
and we need two different reports.
Order sales ship sales side by side,
but then we need a cross tab report
where we take ordered sales for 2019,
and break it apart into the ordered sales that
were shipped in 2019 and the amount that
was shipped in 2020.
Now in this video, we're going to do the first report
in Excel, and then we'll do the second report over
in PowerBI Desktop.
Now here's the finished side by side report.
In the fact table, I want order date to slice
this table so that sales are calculated for 2019,
but then I need ship date to filter the sales column to give
me the shipped sales for 2019.
Now here in the Excel start file, I'm on the data side
by side sheet.
I have the fact table, and over here I have the date table.
I've already imported them both into the power pivot data
model, so I'll click Manage.
I can see I have both tables.
I actually do have a measure, adding up sales.
We go over to diagram view.
There's no relationship between these two tables.
And what we'd really like for a side by side report,
is I'd like to have this date column connected to order date,
and then a second relationship connected to ship date.
Well we can do the first relationship.
I'm in a drag date to order date.
We can clearly see we have that relationship right now.
Because the relationship is connected to order date,
order date will slice total sales,
and we'll get the correct amount for ordered sales.
If we create a pivot table, and I'm
going to use the button over in the PowerPivot window
for pivot table, existing, collapse.
That's fine.
Click OK.
From date, if I drag year down to Rows, from F sales,
total sales.
It does not have the correct label at the top
to tell us that these are ordered sales,
but those are the correct amounts.
So if we have a single date in the fact table and a date
table, everything works fine.
Alt-Tab, if I could simply drag date over to shipping date.
It would be perfect if I could create the second relationship,
but it's telling me, no, I can't do that.
Click OK.
Well, we can do it, if we go up to Design, Relationships,
Create Relationship.
For the first table, I'm going to select Sales,
and this time I want ship date.
And the second table is date, and I'm
going to use the same column.
Now if I try this it will not work,
but I'm going to uncheck Active, and when I click OK,
look at that.
I get a dot dot dot relationship.
That dotted line means it's an inactive relationship,
and that inactive relationship works with a special DAX
function called USERELATIONSHIP.
Home, Data View, down below total sales.
We'll call this shipped sales, colon equal sign
and we're going to use the calculate function.
And I can either create the sum measure again,
or I could just use that measure right there.
And I can use the measure by simply selecting,
calculate can change the filter context.
Remember, automatically, order date is filtering total sales.
But now we do comma, and in the filter we use USERELATIONSHIP.
Now USERELATIONSHIP can only be used inside the calculate,
and it works specifically with that dot
dot dot inactive relationship.
Now there is an order to the columns,
and if you go read help it says always
put the foreign key and then the primary key.
But it's so funny, right in help and built
in the code for USERELATIONSHIP, if you put them
in the wrong order USERELATIONSHIP is programed
to put them in the right order.
So I'm going to say fSales ShipDate.
And that's the foreign key, comma.
dDate, that's the primary key, close parentheses,
close parentheses, and Enter.
Looks like we get the correct total there.
Add some number formatting.
Alt-Tab.
Over here I see shipped sales.
I drag it down, and now that total sales measure
is using the inactive relationship.
Now, I don't like that name there.
Alt-Tab, up in the formula bar, ordered sales and Enter.
And now, we can see we have our side-by-side report, where
ordered sales is using the active relationship
and shipped sales is using the inactive relationship.
Now, our next report is a cross tab.
And a cross-tabulated report needs the same measure
for both date attributes.
Whereas up here, we had a single data
attribute from a single date table, we had two relationships
and then we could access the relationships differently
for two different measures.
But down here, what are we going to do?
We have a single measure.
The solution is going to come from creating two date tables.
We'll have a date table for order year,
and one for ship year.
Now, here's the Power BI Desktop file we're going to use.
And these two tables are imported
from that Excel file we just used.
Now, when you download these, as we
saw in an earlier video in this class,
since these came from a file on my computer,
when you download the files on your computer,
the queries in this Power BI Desktop file are not connected.
I added a parameter.
So you can simply come up in this start file,
go up to Edit Parameters, and you put the file path here
for your computer.
All right.
So we have to create two date tables.
I'm actually going to do this in Power Query.
Up here, I'm going to click Edit Queries.
There's the parameter.
Here's the date table.
The first thing I'm going to do is
I'm going to change the name here.
I'm going to say dDateOrder.
And I want to add a column that has our attribute.
But not just a year, I want it to have a prefix of OY
for Order Year.
Now, I could just go up to Add Column, Add Custom Column.
This is a simple formula.
But, man, Power Query is so amazing.
I select the column up in Add Column, From Text.
I can say Format, and Add Prefix.
Type OY and a Space, click OK.
Now, I have an informative label I can drag into the row area.
We want to rename this.
So up in the Formula bar Table.AddColumns,
I'm going to double-click the second argument.
And instead of prefix, we'll call this Order Year and Enter.
Now, I want to copy this date table so we have a shipped date
table.
Right-click, Reference.
Anything we do here will be reflected in the second query.
F2 and we want to name this.
We'll call it dDateShipper and Enter.
Now, I don't want Order Year-- right-click Remove.
And with year selected, Add Column, From Text format,
and we'll add a prefix--
SY Space and Enter.
Up in the formula bar, we don't want prefix.
This will be called Ship Year, and Enter.
Now, we have our two date tables,
we can go to Home, Close, and Apply.
Close closes Power Query, apply sends these to the data model.
Over in relationship, I want to create a relationship-- so
Date, over to Ship Date.
And it looks like it did not do what we want.
Right-click Properties-- here's our one to many relationship,
so we'll have to change this to one to many.
And I do not want it flowing both,
I only want it from the date dimension table,
having the filter flow to the fact table.
So I select single, click OK.
Now, we've done our data modeling,
we can create a single measure and have Order Year and Ship
Year filter from two different date dimension tables.
Over in Report View, click in the white
over in Visualizations.
We want Matrix from OrderDate, Order Year, down to rows.
Boy, look at this new, white user interface in the latest
update in Power BI desktop.
I like it.
From dDate, Ship Year.
And over from Sales, check the measure.
It's down in values where we want it.
I want to format this.
Grid-- we'll say 15 points.
And there is our cross-tab report using two different date
dimension tables.
All right.
So in this video, we saw how to use two date dimension
tables when we want to cross-tab working on one measure.
And over in Excel PowerPivot, we created
two different relationships from a single date dimension table.
One of the relationships was inactive,
and we wanted two different measures side-by-side.
And, of course, we accessed that inactive relationship inside
of calculate using Use Relationship.
All right.
If you liked that video, be sure to click that thumbs up,
leave a comment, and subscribe, because there's always
lots more videos to come from Excel is Fun.
All right.
We'll see you next video.