ABC of Power BI: H is for historic balanceby
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.
Consider the simple visual from a Power BI report below:
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:
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.
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:
Example DAX code (Measure)
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.
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.
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.
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.
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.
- Accounting WEB: How and why to set up a Dates table in Power BI – a beginners guide
- AccountingWEB: How to create a simple, dynamic Power BI dates table using DAX
- AccountingWEB: An introduction to the Filter Context
- YouTube: How to create a simple, dynamic Power BI dates table using Power Query
- Microsoft: Power Query M Function Reference
You might also be interested in
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs,...