[MUSIC PLAYING] Welcome to Excel Magic
Trick number 1,553. Hey, in this video,
we've got to talk about how to create a
running total in a column. And we want to see how to do it
with a worksheet formula, Power Query M Code, and a DAX formula. Now, this question comes
from Geert at YouTube. Here's our proper data set. And as we can see in an Excel
sheet, we can sort the dates. Here's the units. And we have two separate goals. How do we do just
a running total? Well, that's just adding as
we go, so 120 plus 50 is 170. 170 plus 50 is 220. But that's for the entire time
period spanning multiple years. We also want to see
how to do it by month-- so 120 all the way to 220. But then when it
hits a new month, we need to restart
the running total. Also when we get to
January in the next year, we need to start, not a
continuation of this month up here. So we'll see how to do it with
worksheet formulas, Power Query M Code, and DAX
calculated columns. Now, below the video, you
can download this file and follow along. So with worksheet
formulas, a running total-- if this is sorted-- is going to be quick and easy. Well, we're trying
to add, so I'm going to use that SUM function. I want to select Units,
Shift-Colon, close parentheses. Now, that's a silly formula,
because if I copy it down, it would just be a
relative cell reference. So very carefully I
click in the first-- C7. And when I see my
cursor flashing, and I hit F4 once and twice. I'm locking the 7 row reference,
but not the second one. So as I copy down, that will
remain 7 all the way down. But the second one will
move to 8, 9, 10, and so on. That is an example of
an expandable range. Control-Enter to put the formula
in the cell and keep the cell selected. Click and drag to copy it down. I immediately point
to the smart tag, click Fill Without Formatting. If I go down a few rows
and hit the F2 key, that is an expandable range. The 7 is locked, but
the second row reference is free to move as we
copy the formula down. Now, the next thing we want to
do is we want to change this. We don't want 240 there. I want it to start over
when it gets to a new month. The way we're going
to do this is we're going to use the
SUMIFS function. The sum range where
we're going to use our same expandable range trick. So I click in
Units, Shift-Colon. We want to lock the first 7-- F4, F4. That's our sum range
comma criteria range. We need to look through
the dates, Shift-Colon, F4 once and twice, comma. Now, we have expandable ranges. So as we copy down,
the formulas will only look at whatever the
current row is and previous. And when we're adding
for a particular month, we always have a lower
and an upper limit-- the first of the month
and the end of the month. Well, the end of
the month condition does not need to be
included, because we have expandable ranges. So at any point as we
copy the formula down, the date in the row
will be the upper limit. So all we need is one condition. We just need to say, hey,
from the Date column, please get all the dates
greater than the first day from the previous month. So in criteria one, we need
an operator, in double quotes, greater than, end double quotes. And we need to join it to the
End of the Month function. Now, as I copy down,
I'm always going to look at whatever the
date is for the row, comma. And I need to get
the end of the month. Minus 1 says give me
the end of the month from the previous month. Close parentheses
on end of the month, close parentheses on SUMIFS. And that formula will work
for running total by month. Control-Enter, double
click, and send it down. Point to the smart tag,
Fill Without Formatting. And you've gotta be kidding me. Look at that. For April, that's
the running total. There's only one 6. And then when we get down to
January for the next year, we have 50, 110, and 140. Exactly what we want. Now, when we're creating
formulas in a worksheet-- F2-- notice what
we're allowed to do. This is a formula. And inside our formula, we
just use cell references to refer to either
something in the current row or the whole column. That's much different
than Power Query and DAX. For example, in
Power Query, when we need to refer to
the whole column, we'll use Table.SelectRows. And when we want
the current row, we'll use Table.AddColumns. And we'll have back-to-back
custom functions. When we get over to
DAX, when we need to refer to the whole column,
we'll use the FILTER function. And when we want to
refer to the current row, we'll use the EARLIER function. All right, I'm going
to click Escape. In order to create our Power
Query and DAX solution, because our data comes
from an Excel worksheet, our data has to be
converted to an Excel table. Now, I've already converted
this to a table and named it. So now we're going to bring this
one Excel table into the Power Query editor, and then we're
going to refer to that import and create one Power Query
solution and one DAX solution. So with a single cell selected,
I go up to the Data ribbon tab, get in Transform
from Table Range. The Power Query editor opens. On the left I'm going
to expose the queries. This is our source data. We can see it here. I want to reference this twice,
so right click, Reference. I see it selected. I hit F2 to rename it-- something like Running
Total Power Query. Right click, Reference. F2. Running Total DAX and Enter. Now, I already see
that mistakenly I have data type Date/Time,
and that's not what I want. So I come back to
the original query, I change the data type
here to just Date. It's going to ask me,
do I want to replace? I definitely want to
replace the Date/Time. Replace current. And the beautiful
thing about referencing is that anything I do to this
query or to the source data will be reflected in
both the Power Query solution and the DAX solution. Now, I want to load all three
of these as a connection only to start. So Close and Load,
Close and Load To, Only Create a Connection. Click OK. Our three queries
have been loaded. Now, over in Queries
and Connection, let's double click,
because we're going to work on this running
total for Power Query. Now here's our two columns. And the first thing we
want to do is for each row, we want to have the
first of the month. So I select Date, Add Column. Over here to From Date and
Time, drop down on Date, Month, and we want
Start of the Month. Similar to our
Excel formula where we used the last day
of the previous month, this is going to be our
marker for the lower limit. Now, in our next
step, we're actually going to have to refer
to the date in each row, and also the whole table. The problem is,
in the next step, if we refer to the
whole table, every time that formula gets copied
down for each row, it's going to have to
refer to the whole table. And instead of having
our formula go back each time to get
the table, we're going to buffer the table. That means we can use
it for every single row without making a call
back to get the table. And the way we can
buffer this table-- which, by the way,
that's the name of the output for this
step, that table-- is we come up to the
Formula bar, click f of x. There's the name of the table. After the equal sign,
Table.Buffer, open parentheses, and at the end, close
parentheses, and Enter. Now, down here in
Applied Steps, F2. And we're going to name this
BufferedTable and Enter. Now we want to add
a custom column. And we're going to learn a few
things about custom columns and about custom function. So up in Add Column,
Add Custom Column. The name of this new
column, we'll just call it RT for Running Total. And I want to just
put the number 1 just to see what happens
when I use a custom column. When I click OK, we'll see
the Table.AddColumn function. It's looking at
the previous step. There it is. That's the name of the column. And the third argument
is a custom function. Now, the word each, that
is shorthand for a custom function. Now, for example,
when you use each, if I wanted to do a calculation
for each row in this table, after each, I'm allowed to
refer to the whole column. But as I copy the formula
down, the item from each row will be used. So if I say square
bracket Units, because I'm running a
custom function using each, it will grab each
one of the values and put them in this
column, and Enter. Now, that's silly, but
of course a formula like plus 1, that shows us that
we have the ability to grab the value from each row. You can think of
this custom function and how it's going
down the column and pulling out the
value for each row exactly like Row Context when
we're using a DAX Calculated Column. And we'll see that
later in this video. Another thing we want to learn
about custom functions and this each-- the each makes
it easy for functions like Table.AddColumns,
because we just type each and then the name of
the column in square brackets. And that pulls
out the row value. If we wanted to not
use each and explicitly type out the syntax for
a custom M Code function, we type open parentheses, and
then the name of the variable. And I'm going to call
this Outside Table-- OT-- close parentheses. That's the variable we're going
to use inside the parentheses. Once you define
the variable, you have to use the Go To operator. Equal, greater than, space. And then everything that
comes after the Go To is how we use that variable
in whatever calculation. Our calculation still
needs to be this. But if I hit Enter, that
syntax only works with each. When you actually explicitly
define your variable, then for whatever
the column is where you want to grab the
row item, you actually have to explicitly preface
it with the variable name. So when I hit Enter,
that will work. Now, this is more complicated
than using the each and just the field name in
square brackets. But because we're going to have
two functions back-to-back, and we're going to need
to distinguish variables from this outside table
where the formula is attached to this table and making a
calculation from each row, and then an Inside Table
inside of Table.SelectRows where we're going to
be filtering the rows, I'm going to choose to
explicitly create our M Code functions with variables
and go to Operators. Now, I want a little
bit more room here. Close the queries on the left
and expand the Formula bar. Now we're going to use the
Table.SelectRows function as part of the OT
custom function. Now, think about this. What are we doing? Table.AddColumns, that's
attached to this Outside Table, and it's going to make a
calculation for each row. But coming next, we're going
to have Table.SelectRows. And so for each
row in this table, there'll be a table inside this
cell that we're making actions. All right, you ready? I'm going to highlight
that and delete. Table.SelectRows, open
parentheses, and we're going to use Control-C.
That same table. But now there's going to be a
mini table inside of each cell. Comma, and Table.SelectRows,
just like Table.AddColumns, is going to make a
row-by-row calculation. But this'll be internally
inside each one of the individual cells. Comma space, and we're going
to define our second custom function. The variable name
is Inside Table. That means we're going to
be referring to the columns inside of this table. Close parentheses, Go To, and
I need to filter this table. So as I copy down
the rows, we're in essence going to get an
expandable range like we had over in the Excel worksheet. I need the Inside Table,
the whole Date column, close square bracket. And that's the entire column. So you could visualize
this entire column inside Table.SelectRows, but
it's coming from that table. And now I need to say how
many of you are less than or equal to the actual row
value from the Outside Table. So this is where we
say, Outside Table, square bracket, Date,
close square bracket. And now close parentheses
on Table.SelectRows. Close parentheses. When we hit Enter, we've
got a table in each row. I could select any single cell,
and no way, Table.SelectRows is definitely filtering. If I go down to this row, I can
see the entire filtered table. Table.AddColumns, that's
iterating this entire table. Inside Table.SelectRows is
iterating that buffer table. If we think back to
what we did with Excel, we used an expandable range. Here you can see the
first four dates. In Power Query, we had
to use Table.AddColumns and Table.SelectRows. As we copy our formula down, we
get the same exact expandable range. Now, we do not want the table. We actually want the Units
column, because remember, we're ultimately trying
to add those units. Now I'm going to come up to
the formula and Backspace. That entire
Table.SelectRows is a table. And in Power Query,
if you have a table, you can do a two-way
lookup on this table. You can actually
visualize it down here. If we had the positional
index operator, we could pick out the row. If we have a field
access operator, we can pick out a column. And that's exactly what we want. From this table, we
want the Units column. Field access
operator, because it's coming after a
table object, when I do square brackets,
Units, close square bracket, that tells the table I only
want this particular column. Now, we want to notice
something about M Code. Those square brackets are
called a field access operator. Because it's coming
after a table, it knows to get
the whole column. Right here, though, we use
the same field access operator on Date. But because that field
access operator on Date is operating in the context
of Table.AddColumns, it's iterating over
this BufferedTable. This is the whole
table, so it knows to pull out the individual
item for each row. Now, when, we close parentheses
on Table.AddColumns, this whole construction
looking up the column will deliver a list. So when I hit Enter,
there's a list. And look at that expandable
range as we copy it down. Now we almost have
our final result. All we need to do is
add the list of numbers. So before
Table.SelectRows, we'll have a function that's
called List.Sum. All the aggregate functions-- SUM, AVERAGE, MIN, and so on-- will work on list. I better spell it right. So now I come to the
end, close parentheses. And when I hit Enter, that
is absolutely beautiful. That whole formula-- Table.AddColumns,
Table.SelectRows, List.Sum, and our two custom functions
OT and IT-- are doing the job. Now, one last thing. We need a data type. And the fourth argument
and Table.AddColumns, if we come to the end,
comma, type, and we'll say number to give us a
decimal data type, and Enter. And there's our data type. Now, I'm going to come
over to Applied Steps. F2. I'm going to call
this RT and Enter. That's the name of the step. Now I want to add our next
column, Running Total by Month. And we're going to use
almost the same formula. So highlight, Control-C, Escape. Come over to f of x. Click, Control-V. Now,
this won't quite work, because it's working
on three steps up, and I wanted to work on RT. Table.AddColumns needs to
know to add a column to RT. So in the first
argument, I type RT. And this is going to be the name
of the new column, RT by Month. And now inside of
Table.SelectRows, we need to add a second condition. There's the first condition-- Inside Table right
there less than or equal to Outside Table Date. So after Date, the operator
for an and logical test is all lowercase and, Inside
Table, field access operator to get the full
column, square bracket. And we're trying to look
at start of the month. And start of the month as
the lower limit is included. So I have to say,
hey, are any of you greater than or equal
to Outside Table? Field access operator to get
the individual item in each row. And I better spell this
right, and case too. Close square bracket. And now when I hit Enter,
here's the running total. But when it gets to
April, it starts over. And also when it gets
to January for 2020, it starts a new running total. Now I'm going to come over
here and F2, call this Running Total Month. Now, I've already loaded
this as a connection only. So I'm in a click Load, load
it as a Connection Only. Right click. We're going to change
the load location. Load To. I want to load it right
there, Table, Existing. Let's say K6. Click OK. Now that's looking good,
but I want to edit this. I don't want the
start of the month, So I'm going to come
up, double click. Watch this. I'm going to cheat. I'm going to come up to
Insert, Start of the Month. And I'm noticing there's a
Date.StartOfMonth function. And in the formula
down here, I am going to have to go
to the Outside Table. I was looking at
start of the month, but now I want to look at Date. So I'm going to copy that-- Control-C. Come down to RTM. Drop down. And right here very carefully,
Control-V. And the date has to come from
the Outside Table. So OT and Enter. There we go. Now I can come up,
delete this step, Delete, and we have our
result. Now actually, these formulas
will work no matter how this table is sorted. But just in case
the external table gets sorted in some
other direction, I want to visually see it
so these dates are sorted. So I'm going to add a sort,
which will end up as a step here, Ascending. Now we have our
finished running totals. Now we can Close and Load too. And so using Power Query,
M Code, custom function, and some built-in functions,
we have a Running Total and a Running Total
by Month in a column. Now we want to see
how to do it with DAX. Now, the source data
is the same Excel table that we're going to use
for our DAX solution, as was the source data that
we used for the Power Query solution. Now normally, we take
an Excel table and we'd use this button right here-- Add to the Data Model. But I actually brought
this table in once to the Power Query editor
and then referenced it. And the reason why is if I
use this button right here, it'll take the Excel table
name, and I didn't want that. I wanted a different name
over in the data model. So that's why I
loaded it this way. But now we need to load
this to the data model, because it's connection only. So right click, Load To. Now I check, Add This
to the Data Model. Click OK. It's loaded. Now Power Pivot,
Manage Data Model. That opens the Power
Pivot for Excel window. I can see our table right here. This column has Date/Time
number formatting. It properly imported as
a date, because that's the data type from Power Query. But I'm actually
going to format this. This is not necessary. This is just something
I like to do. And our goal is to create
our running total here. So I'm going to double click
the calculated column header and call this RT
for Running Total. Now, this is a
calculated column, so we come up to
the Formula bar. Now ultimately, we
need to add unit. So watch this. I'm going to use an
aggregate DAX function-- SUM. Because I'm in Power Pivot, I
can simply click on the column that I want to add. It puts the proper syntax, table
name, and in square brackets, the field name. Close parentheses. And when I hit Enter, I get the
same grand overall total number all the way down. When we use an
aggregate function in a calculated column,
this will always happen. If we had used just the column
not in an aggregate function, then it would have
pulled out using row context the items for each row. But now we have this
aggregate function, and we actually want the ability
to filter that Units column. So as we copy down, we
get our expandable range. That means we need to
change the filter context. And the function we use to
change the filter context is the CALCULATE function. So I typed the letter C, I
see the CALCULATE function highlighted in blue, I hit Tab. CALCULATE takes some
expression, and then whatever filters
we put here, that changes the filter context. So I come to the end,
comma, and in filter, as the formula
automatically copies down, I somehow need to get
that expandable range for our running total. So just as we did over in Power
Query and M Code, over there, we use Table.SelectRows. Well here, in DAX,
the function that allows us to filter a
table, the FILTER function. The first argument-- what's
the name of the table? RTDAX. I see it highlighted
in blue, so I Tab. Comma. This is a full table
inside of each cell in the calculated column. And that table right
there has columns, just like we saw a moment
ago over in our M Code. Well, the column in that table
that I want to use to filter it is RT, Down Arrow to Date. So now I have this Date
column from that table, and I'm going to
try and filter it. I'm going to say, any of you
that are less center equal to. And whereas here we have the
full column from that table, I now need to get back
to the Outside Table and get the row date for each
row as the formula copies down. If I were to select
this column, it would be just like this one--
the full column referring to this filter table,
but the way I jump back. And in DAX, we talk
about iterating in the FILTER function
over this table. But now I need to jump
back past this iterating and get back to the iterating
over the outside table. And the function we use to
jump to the outside table and iterate over that
table is EARLIER. Now I come to the end, close
parentheses, and Enter. And there we go. We get our grand
overall running total. That is the upper limit, right? So as we copy down
right under 360, the Date column is
looking at 4/13/19 and taking everything
equal to or less than. And then because of
the FILTER argument here, it provides a
filtered table, which is this table right here,
and filters the SUM function to just the values we want. Now let's copy this-- Control-C, Escape. Double click the second column. We'll call this RTM
for month, and Enter. Up in the Formula
bar, Control-V. And I want to
Backspace, Backspace. Now we're back to the
FILTER expression, and there is only one FILTER
expression inside of filter. So if we want to run an and
logical test, over in M Code, we used lowercase and. Here we use &&. That tells the
filter now it's going to have a second condition. And the second condition
is the same column. Watch this. I can just click here. That puts it in
instead of typing it. And I need the lower limit now. So I say greater than. And guess what, just like in
our SUMIFS formula in the Excel worksheet, I'm going to
use end of the month. Now, the start date, I need
to get to the Outside Table. So E, EARLIER. Now I can click on this column
here just to put it in quickly. And because it's in
EARLIER, now it's not getting the full column
from the FILTER table. It's getting row-by-row
dates from the Date column. Close parentheses
on EARLIER, comma, and I need to jump back to the
last day in the previous month. So minus 1. That gives me the end
of the last month. Close parentheses. Now we have one, two logical
tests on the same Date column using &&. I close parentheses on FILTER. FILTER argument of CALCULATE. Now FILTER will deliver
a full table that meets both conditions,
and CALCULATE will filter the table, and
the units will be correct. Close parentheses and Enter. Now, look at that. Now we have running
totals starting over when we get to
April, running total when we get to January in 2020. Now, look at this. We actually used the Date
column earlier twice. An alternative to doing this
is we can run a variable. And if we run a
variable before we ever get to the FILTER
function, then we don't have to worry about this
iterating and jumping back. So I'm going to cheat. I'm going to copy this-- Control-C, Escape. We're going to call
this RTMonth-Var. And I'm going to
define a variable Var. And we can see it in green,
so it is a legitimate element we can put into the formula. Then we type the name we want. That's the name of the variable. Then I type equals. And watch this. I'm just going to
highlight the whole column. That means as it copies
down, this variable is calculated as if it
was sitting in the row, automatically getting
the row context from the row-by-row iteration. Now I type a Space, and
I have to type Return. And then whatever comes
after is the formula. Control-V. So I
deleted most of that. We have our CALCULATE, our SUM. We need to filter using
CALCULATE on that table to get the correct running total. So we're going to
filter this table. I'm going to select
that column right there. And I'm going to say,
hey column, greater than or equal to the lower
limit end of the month-- M-O-N. And look at this. In Excel Power Pivot
Data Model DAX, xy means that's a legitimate
variable that I can hit Tab and use in my formula. Comma, minus 1. That jumps back and
will be our lower limit. I'd better not have
the equal sign, because that's the last day
from the previous month. And && Space. There's the Date
column, but it's working on that Date column there. You also have to be less
than or equal to M-O-N. That'll be whatever
the row date is. Tab. And so for FILTER,
I close parentheses. For CALCULATE, close
parentheses, and Enter. Now, if we compare these two-- I kind of like
using the EARLIER. But some of the
advantages that you might gain with a variable-- well, it only has to calculate
once at the very beginning, then that static
value is used twice. So there might be some
performance advantage. Also, some people like the
look of using a variable. And so that's a second way to
create running total by month. Now, wait a second. This is in the data model. And really, this
is just an exercise of how to do running
totals in the data model in a calculated
column, as Geert asked. This isn't something we can
easily load to the Excel sheet. I'm going to save this-- Control-S. When I Control-S,
those columns actually got saved to the data model
in the columnar database. Now I'm going to close this. And just for kicks, I want
to bring this out here. In essence, I want
to show you how to do a DAX query to
look into the data model and pull something out. Now, in Excel, this is not
real obvious how to do this, and there are a few different
ways we could do this. The way I like to do
it is I go to Data, get in Transform,
Existing Connections. And our query, which got
loaded to the data model-- there it is right there. So I click and click Open. Look at that. This is the same
Import dialog box as we get when we load
something using Power Query. I want it as a table
in the existing cell. Click OK. And now there you go. We have our running totals
and running total by month using DAX. Now to recap, in Excel, we
created our expandable ranges using columns and
cell references. In Power Query, we created our
expandable ranges and running totals using Table.AddColumns
and Table.SelectRows and two custom functions. And then in DAX, we got to
see it two different ways-- CALCULATE, SUM, FILTER,
EARLIER, and End of Month. And then we saw a second
way in DAX using a variable. All right, if you
like 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. [MUSIC PLAYING]
