[MUSIC PLAYING]
Welcome to MSPTDA video number 29.
Yes, Microsoft Power Tools for Data Analysis.
And in this video, we want to see
how to create two different reports when we have order date
and ship date in the fact table or the transaction table.
And in this video, we'll see how to do it
with worksheet formulas.
Next video we'll see the same example,
but we'll use data modeling and DAX formulas.
Now, here's the name of the file we're going to use.
You can download it in the link below the video.
In our fact table, we have sales transactions.
But each sale has an order date and a ship date.
And our goal is to create this side-by-side report that has
ordered and delivered sales side-by-side and then a cross
tab report where we take the ordered sales for a particular
year and break it into the two parts--
the amount that was shipped in 2019 and the amount that
is shipped in 2020.
Notice the totals are the same.
All right.
So we're going to go over to Data and Reports.
Now our formula goal for our side-by-side report
is to use the criteria at the head of the row that's
the beginning of the year.
Then inside the formula, we will create the end of the year.
Then we'll have our two conditions and criteria
to look through the order date, pick out the sales, and then
the ship date and pick out the sales.
So the perfect function of course is SUMIFS.
The sum range-- that's our sales.
Now this needs to be locked in all directions
so I hit the F4 key.
Comma, criteria_range1.
Ordered and then delivered, which is ship.
So I first select Order Date.
And I want you to notice that when
we copy this formula to the side,
we need that orange column to move.
So I'm going to hit the F4 key once and twice.
We've locked the row references so as it copies down,
it remains locked on rows 3 to 20.
But notice the Bs don't have a dollar sign.
So when we copy to the side, that B column
will move to the C column.
So that's perfect.
Comma.
Now criteria 1.
When we're looking through dates for a particular period,
we always have a lower and an upper limit.
This is the lower limit, so I have
to ask the question of the dates.
How many of you are greater than or equal to the lower limit?
We put our comparitive operator in double quotes.
Greater than, equal.
That's two symbols.
End double quote.
Then we have to use the ampersand, the joint operator,
Shift-7.
That allows us to join it to the date.
Now when we copy the formula to the side,
I definitely need a lock on 1/1/2019.
But when I copy the formula down,
I need that purple cell to move to the next row.
So here we hit the F4 key one, two, three times.
Lock the column but not the row.
Now comma, criteria 2.
Well, I'm going to use the same range as criteria 1.
So I click on that argument.
Control-C. Click on criteria 2, Control-V.
Comma.
Now we have an upper limit.
Double quotes.
Less than or equal to.
End double quotes.
And we'll join it to-- uh-oh.
We need to create from the beginning of the year
the end of the year date.
No problem.
We use the end of the month function.
Start date.
I'm going to hit the F4 key one, two, three times.
Comma.
And how many months do we need to jump into the future?
11.
0 gives us the end of January.
So to get to December, we type 11.
And that will work.
Close parentheses on end of month.
Close parentheses on SUMIFs.
Control-Enter.
I already have the SUM function there.
Double-click and send it down.
Copy it to the side.
Now I want to check two cells.
F2, I'm verifying that I have order data
and sales and the correct condition criteria.
I do.
Tab, F2.
There I have ship date and sales, start of the year.
Now I don't really want to see the first
of the year on my report, so I'm going to highlight these sales.
And notice the formulas are using that as the lower limit.
Control-1.
Number tab, custom.
And up in Type, I just put "yyy."
There it is.
Click OK.
So our report gets the year, but our formulas
get the first of the year.
Now I'd like to highlight all of these numbers,
and I don't want 0s to show up as 0s.
I want to show nothing just like a pivot table would.
Control-1.
We're going to start by selecting number.
I want to use a separator.
When I come down to custom, that's
the code that shows 1,000 separator and two decimals.
But the way custom formatting works
is that there's actually four sections.
So I'm going to copy this, and when I type a semicolon,
now my custom formatting has two sections.
The first section is for positive numbers,
the second section is for negative numbers.
And if I left it empty, it would show nothing.
But I want to type minus and then Control-V. I
want to use the same number formatting here
for negative numbers but show a negative sign.
Now when I type the second semicolon,
I get the third section, which is how you want 0s to display.
And if we leave it empty, that's exactly what custom formatting
will display in the cell.
Now the fourth section is for text,
but we don't have to worry about that.
And I'm going to copy this, Control-C. Click OK.
And that is looking good.
Now down here for our second report,
I'm highlighting Control-1.
Custom.
And in type, I Control-V. Click OK.
Now for our row headers, I want to show the year
just like up here.
But let's add OY for Order Year.
Control-1.
And before, 4 ys in double quotes.
O, Y, space, and double quotes.
And we can already see the preview up there.
Click OK.
Remember, those beginning of the year serial number dates
are still underneath the custom number
formatting that we get to use in our formulas.
Similarly, Control-1 will do ship year in double quotes
with the space and then for ys.
Click OK.
Now we'll create our formula here.
We're still trying to add using SUMIFs.
But we have two conditions.
One at the head of the row and one at the top of the column.
Order year, we'll look at order date.
Ship year, we'll look at, ship date.
Equals SUMIFs.
SUM range.
We'll highlight.
F4.
Comma.
criteria_range1.
I'm going to pick order date.
And this is locked in all directions--
F4-- whereas up here, we wanted two separate calculations
each using a different date column.
Of course, in a cross tab, we need
to look at both columns in all the cells.
Comma.
Criteria 1.
We'll do the lower limit and we'll
join that operator to H15.
And lock and hit the F4 key one, two, three times.
Comma.
The date column.
F4.
Comma.
Criteria 2 is the upper limit.
And we're going to do end of month.
F4 key one, two, three times.
A comma, and 11 months.
Close parentheses.
Now every single cell in our cross tab
will have the upper and lower limit for order date.
Comma.
Now we repeat for criteria range 3 and for 4,
but now we use ship date.
F4.
Locking it in all directions.
Comma.
And the lower limit.
And whereas the row hitter needed
to be locked going across the columns,
when we copy this formula down, I need it locked on row 14.
So I hit the F4 key once and twice.
Comma.
Ship date.
F4.
Comma.
Criteria 4.
This is the upper limit, so we have to say less than
or equal to and join it to end of the month.
Same thing-- we're going to hit the F4
key to lock the row reference inside of end of month.
Comma, 11.
Close parentheses on end of month.
Wow.
So we have criteria 4 matched up with the correct criteria 4
range.
Three, two, one, and our SUM range.
Very carefully, I click at the end, close parentheses.
Control-Enter.
Copy it to the side.
I can already see there's trouble.
Copy it down.
I'm going to go to the last cell diagonally furthest
away and hit the F2 key.
And I can already see right there this is the lower limit.
So whatever is over in that column over there
with our dates has to be greater than or equal to.
Now watch this.
I'm going to do a little trick.
I edited this and I need to distribute it
through the entire range.
So with the whole range highlighted and the active cell
in white, I hit F2.
Because I have the whole range highlighted,
when I use Control-Enter to repopulate
the formula through the entire range,
now if I go to the upper cell and hit F2,
cell references are working.
And there it is.
We've broken apart 2019 ordered sales
to when they were shipped.
Some in 2019, some in 2020.
All right.
That was a little fun with how to deal
with two different dates in a fact table.
We saw side-by-side report, and we also saw a cross tab report.
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, including video number 30.
We'll see how to do the same problem
but using data modeling and DAX.
All right.
We'll see you next video.