Power BI with your accounting data

DAX Running Total Calculation in Power BI

Posted by Hugh Johnson on 29-Aug-2018 19:04:03
Hugh Johnson
Find me on:

What is a running total?

A running total is the summation of a sequence of numbers that is updated each time a new number is added to the sequence. It is done by adding the value of the new number to the previous running total.  There are three main reasons to calculate running totals.  

  1. Keeping a running total enables the total, or aggregate to be expressed at any time without having to sum the whole sequence again.
  2. By keeping a running total, there is no need to maintain a record of the underlying sequence if all you need is the total.
  3. If you want to analyse trends where there is a lot of volatility from one number to the next, or if we want to put the numbers into a more digestible context, then a running total or a rolling variant of this is very useful.

In Power BI though, points one and two can be problematic to implement, though perhaps not that relevant in the context of analysing small to medium sized business accounting data.  When you refresh your Power BI dataset it dumps your entire dataset and replaces it with the refreshed one.  This makes it impossible to maintain your running total from one refresh to the next.  You will either have to maintain this running total in your source dataset, or always include all elements of the sequence in your dataset, from which you recalculate your running total in Power BI.

If you do always include all the elements of your sequence, then this allows you to calculate running totals on the fly that you can use to analyse trends as well as slice and dice the data (depending on its makeup).

A running total showing sales in the last 28 days could be a very useful way to track trends right up to close of business yesterday. This would produce much more timely insights into your sales patterns than waiting to the calendar month end, and looking at a chart of daily sales would often be difficult to interpret.

Running total vs rolling total vs total sum

A running total across a sequence of data points (e.g. sales over the last 28 days) will give the same result as simply summing up the total sales for all of the last 28 days.  The key difference is how the calculation is made. A total sum is performed by adding each number in the series while strictly speaking a running total simply adds the latest number in the sequence to the last running total value.  A rolling total would be where you are continuously evaluating the same number of data points in the sequence and as you include the latest data point you drop the earliest. Sales over the last 28 days would be a classic example.

Now in Power BI, the way that we do running totals is kind of a hybrid of all of these.  Under the covers, Power BI is optimized to be able to iterate over and sum extremely large datasets very quickly, so there is little to be gained by just maintaining a running total and dispensing with the underlying data.  In fact generally you may not want to do this as you will lose the opportunity to slice your result by dimensions such as customer, country, currency etc.. So when we calculate a running total in Power BI we are actually doing a total sum of all the selected values from the first to the last in the sequence.

Calculating a running total in Power BI

Firstly, unless you have a very very good reason to do otherwise, I would create a running total in Power BI as a “Measure” and not a “Calculated Column”.  The reason for this is that a running total is generally a summary number. Take a look at this article that explains the difference between Measures and Calculated Columns.

There are two ways by which you can create a measure in Power BI.  A "Quick Measure" or by writing your own DAX code.

Running Total Quick Measure

Power BI provides a facility to create a Quick Measure, that saves you from having to write your own DAX code.  The idea of Quick Measures is that you are effectively presented with a very simple wizard to complete where you can chose the calculation type, select the base value, add the fields and Power BI will automatically generate the DAX code for you.  "Running Total" happens to be an option within the Quick Measures wizard.  If you want to amend the generated DAX code afterwards (perhaps to add a filter) then you can.

Quick Measure in Power BI

Writing your own Running Total DAX formula

The second option (which is the option that we generally use) is to calculate the running total by creating a new measure from scratch in DAX.

Either way, there are three main functions used for creating this DAX command.

The DAX created by the Quick Measure command uses SUM rather than SUMX.  SUMX gives you greater flexibility to evaluate the running total of a more complex expression rather than a simple sum of the values in a column.

In our Accounts Receivable solution for Sage 50 we have combined these functions to create a new measure to find running total gross sales amount for the last 52 weeks for the selected date and selected customer.

This measure is used to show annualized sales trends, specifically as a backdrop to looking at trends in Days Sales Outstanding

Gross Sales - running total 52 weeks =
CALCULATE (
   SUMX (
      BITransSplits,
      BITransSplits[GrossAmount]
   ),
   BITransSplits[IsReceivable],
   FILTER (
      ALL(BIDates[Date]),
         AND (
            BIDates[Date] > MAX( BIDates[Date] ) - 364,
            BIDates[Date] <= MAX( BIDates[Date]
         )
      )
   )      
)

There are two parts of this DAX formula.  The first is done to compute gross sales and the second part is done to apply date filters to get the rolling total.

Computing the Gross Sales amount

In our dataset, the Gross Sales amount is found in the ‘BITransSplits’ table.  There is a column called [GrossAmount] that is the gross amount for every line item of every transaction.  What we need to to is to filter this table by transaction type, so that we are only selecting sales invoices and credits (receivables).

Since we often want to apply this filter, we have added a Calculated Column called [IsReceivable] into the ‘BITransSplits’ table.  

IsReceivable = 
IF (
   OR ( BITransSplits[Type] = "SI", 
      OR ( BITransSplits[Type] = "SC",
         BITransSplits[Type] = "SD" 
      )
   ),
   TRUE(),
   FALSE()
)

Very simply, [IsReceivable] is TRUE if the transaction type is a Sales Invoice, Sales Credit or a Sales Discount (an early-settlement discount or adjustment to the original invoice value if the invoice is settled early).  A Boolean TRUE/FALSE value makes it really intuitive and easy to use as a filter within a CALCULATE function like we have here.

So the formula to calculate the Gross Sales Amount would just be:

Gross Sales Amount = 
CALCULATE (
   SUMX (
      BITransSplits,
      BITransSplits[GrossAmount]
   ),
   BITransSplits[IsReceivable]
)


But this is not enough.  We are looking to compute for any selected date, the Gross Sales Amount for all the dates going back over the last 52 weeks (364 days).  So we need to add another filter to select this date range. The full construct for the measure is now:

Gross Sales - running total 52 weeks =
CALCULATE (
   SUMX (
      BITransSplits,
      BITransSplits[GrossAmount]
   ),
   BITransSplits[IsReceivable],
   FILTER (
      ALL(BIDates[Date]),
      AND (
         BIDates[Date] > MAX( BIDates[Date] ) - 364,
         BIDates[Date] <= MAX( BIDates[Date] ) 
      )
   )
)

By adding ALL, we ensured that there are no applied filters on our dates. And further we added AND to our command to satisfy the criteria of 52 weeks (364 days).

Topics: Power BI, DAX

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts