Running Total functions

Guides • Data Integration & ETL • 25/02/2018

Overview

Sometimes, you may be given data on which you would like to calculate a running total.

For example, your data might look like this:

Although measures such as running totals can be calculated quite easily with DAX, there are some situations when you may want to pre-compute these values (to save on system resources and compute power later on). This article explains how to calculate running totals, using ‘M’-code.

Steps (Running Total)

1. Load the data to Power Query

When in the query editor, the data should look as follows:

2. Add an Index column

As a continuous running total needs to be created, you need to add an index column, so that a cumulative calculation can be performed.

Add an Index column, starting from ‘1’, so the data looks like this:

3. Add a Custom column

Now, add a custom column, with the following formula, to create the actual running total calculation:

For each row in the table, in the code above, the inner part of the formula, List.Range, returns a list of Sales Values from the 0th row, up to [Index]th row. The outer part of the formula, List.Sum, then sums this list, to return a cumulative sum value, for each row.

When you click ‘Ok’, the data should return an extra column with the cumulative sum.

Finally, remove the Index column as it is no longer needed, so the data looks like this:

Steps (YTD Total)

The steps described earlier show you how to create one calculated column. This returns a running total throughout the entire dataset, from the first record to the last.

What if, however, you wanted to create a running total that resets at the beginning of each year (to calculate, for example, a Year to Date total)?

To do this, you need to take a slightly different approach. The new approach used is summarised below:

Create a function that returns a running total column. This function accepts two parameters: (i) the name of the table with the values; and (ii) the name of the column, with the values to be summed.
Create a 2nd function that returns, the running total over the right range. This function takes three parameters: (i) the name of the table; (ii) the name of the column with the values to be summed; and (iii) the name of the column with the values, over which the running total column should reset.

Let’s walk through this below:

1. Create a running total function - fnRunningTotal

This code below essentially is the same as the query created above in this article, except turned into a function, and instead of returning the whole table, with the extra running total column, it returns just the running total column (as a list).

The ‘M’ code for this is shown below:

Focussing on the inner let / in portion of the code above, after accepting two parameters: the table name, and the of column to be summed (called in the code, tbl, and sumcolumn, respectively), the function:

Adds an Index column starting from one, and which then also increments by one
Renames the column to be summed (as passed through by the sumcolumn parameter) to “Temp”
Adds a custom column, called “Running Total” that, for each row, sums up the list of values held in the “Temp” column, from the very first row in the table, up to the “Index”th row of the table. This is effectively, the actual running total
The last step is new, over the query from before, in that the function extracts and returns only newly created column called [Running Total], and not the whole table.

With the above ‘M’ code, the query should turn into function, like shown below:

If you invoke the function, for example, with the following parameters

tbl = “Sales” – the name of the query with the Sales data
sumcolumn = “Sales” – the name of the column in the Sales query with the Sales data

It will return the following. Note the equivalence of the results with the earlier query in this article

Give the function the following name: fnRunningTotal

2. Group the data

Next, we need to work out a way of calling the above function with a new query

First, group the following data, on the column called: “Year”,

Use the following group by settings:

The data should look like this, with table objects returned to each row:

3. Do not directly add a custom column

Next is what you should not do, but we explain what would happen if you did.

You could next add the following custom column, calling the Running Total function like this:

This will cause the data to look like shown below:

Unfortunately, if you expand the “Running Total” column, you will only be left with the columns “Year”, and “Running Total”, like this:

If, instead, you expand the “Data” column, you will have the “Year”, “Date” and “Sales” columns, but not the “Running Total”, as shown below:

If you expand both the “Data” and “Running Total” columns, you will be left with too many rows, like this:

We, therefore, have to find out a way of merging the “Running Total” list objects, with the “Data” Table objects, as an extra column, before then expanding the “Data” table objects, so there is only one Expand operation. The following steps explain how to do this.

4. Modify the M-code

Returning to just after the Group By step above, the M-code should look as follows:

Note:
In ‘M’ code, ‘each’ is just syntax sugar, or syntax shortcut for (_) = >, so that ‘each _’ means, (_) => _

In the above case, “_” (the underscore) is a throw-away variable, that refers to each table object returned by the Table.Group function

So, you can modify each _ in the above code, so that instead of just returning a table object, it returns each table object with an additional running total column merged onto it. Refer to the ‘M’ code below.

Modify the ‘M’ code as follows:

Focusing on the inner let / in portion of the code above:

The table object, “_” is passed in as a parameter of type table to a new sub-function
The Table.ToColumns function converts each column of the “_” table object, into a list of lists
The ‘&{fnRunningTotal(_ “Sales”) }’ appends the returned ‘running total’ list, into this list of lists
The Table.FromColumns function then converts the list of lists back into a table, with the headings of the original table object, plus “Running Total”, as the heading of the newly appended column

The returned query should now look like this:

5. Expand the ‘Data’ column

Next, expand the column called “Data” (with all the table objects) by adding the following lines of ‘M’-code:

The code expands only the necessary fields in the “Data” column, which are: all the columns of the original table, plus the “Running Total” column, less the “Year” column, as it is already shown in the previous group by step.

The data should finally, look like this:

6. Turn the query into a second function

Finally, convert the above query into a second function, called fnGroupedRunningTotal, by modifying the code as follows:

The query should turn into a function that accepts three parameters:

This can now be invoked and re-used by creating a new query like this:

M-Code

   = fnGroupedRunningTotal(Sales, “Year”, “Sales”)