Director Accounting Insights Ltd.
Share this content
A digital stock market tracking graph
istock_liquidity_solarseven

ABC of Power BI: H is for historic balance

by

It is quite simple to use Power BI to calculate the balance of any account for any date. This can be great if you want to keep a very close eye on liquidity. Hugh Johnson continues his ABC of Power BI series with a historic balance tutorial. 

3rd Nov 2021
Director Accounting Insights Ltd.
Share this content

Consider the simple visual from a Power BI report below: 

Monthly balance

On the face of it, this could be an extract straight from your accounting software. It could be, until I tell you that the fiscal year for this company runs from April to March; Power BI does not need to be tied to a rigid set of periods and fiscal years. 
 
You can eye-ball the data and note the rough range of the bank accounts balance, but the picture is not that clear. What about a closing daily bank balance for the last 12 months, with a trend line?   

Well, here you go: 

Daily balance  

Cash in the bank tends to be low at the start of the month as suppliers are paid and then improves as customers pay their invoices. The intra-month balance swings can be very high. There is also a downward underlying trend over the last 12 months. You would never see this just from a table of monthly closing balances. 

Full page result

The beauty is that I have used the same calculation [ClosingBalanceExample] in the form of a Power BI Measure for both the monthly table and the graph, and can present both together in a single, interactive page. 

Just as you can use this Measure for the bank, you can of course do the same for your debtors, creditors, or total working capital.  This is great for a company that needs to keep a very close eye on liquidity.   

So, how do you do this? 

Historic Balance Calculation in Power BI 

Calculating the historic balance of an account for any date is very straight forward in Power BI, but there are a couple of pre-requisites.   

Firstly, for the account you are analysing, you need to know an ‘opening’ balance, and secondly you need to know all the transactions that have happened between the date that you are calculating for and your 'opening’ balance for the Account(s).   

You then take the opening balance and add (or subtract) the transactions depending on whether your opening balance date is before (or after) your target date. 

The simplest way to do this is if your opening balance is the net balance on the account(s), including all known transactions regardless of date.   

If you do this, then you just need to subtract the transactions that are dated after your target date.  For those of you that use Sage 50 Accounts, this is the NominalRecord[Balance] value. 

The data model 

In its simplest form, the data model should include an Accounts table with the opening balance as described above; a transactions, or Journals table that includes all the dated postings to each of the Accounts; and a Dates table. 
 
It might look like this: 
 
Data Model 

Example DAX code (Measure) 

DAX code

The image below shows the DAX Measure that I used in the graph (and in the balance sheet table) above to calculate the historic balance for any Account for any date over a selected date range. 
Dates 

The Measure [ClosingBalanceExample] is designed to work in a report where you use slicers to select a given date range.  Two Measures [MaxDate] and [MinDate] (outside the scope of this article)  define the upper and lower bounds of the date range for the graph.  If a date value is outside this range, then these Measures will return a blank.  So, rows 15 to 19 of the calculation are simply to make the x-axis of the chart adjust dynamically to the date range selected. 

MaxDate variable 

The MaxDate variable in row 2 returns the Dates[Date] value of the x-axis 

Opening balance – aka BalanceTotal 

The Opening balance referred to earlier in the article is simply the SUM of Accounts[Balance] in the Filter Context – calculated on row 5.   

This assumes that in the data model Accounts[Balance] is the balance of the Account(s) after all known transactions have been posted to the account, regardless of date. 

TransactionsSinceMaxDate 

The TransactionsSinceMaxDate variable sums the Journals[Amount] column (row 9) in a Filter Context modified by CALCULATE (row 8) to include all dates that are greater than MaxDate (rows 10 and 11) 

The result is then simply BalanceTotal – TransactionsSinceMaxDate 

This is a generic calculation 

The beauty of this, as with so many Power BI calculations, is that it is a generic calculation.  It will calculate the historic balance for any, all, or any combination of Accounts over any date range for any date.  In the graph, I have simply applied a filter to show bank accounts. As such it is showing the net balance across all bank accounts. 

Summary 

A generic historic balance calculation in Power BI is very simple and flexible if you have your data model set up correctly.  You can use it to create Balance Sheet reports over any date range, as well as track specific trends for Cash, Debtors, Creditors, Working Capital or specific customers or customer groups. 

Useful links 

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.