ABC of Power BI: ‘J’ is for Journalsby
Continuing his ABC of Power BI series, Hugh Johnson discusses journals as a starting point for financial reporting.
Creating a single table of “journals” in your Power BI model to hold all your transactions, actual or otherwise, is a great starting point for financial reporting.
When I first started using Power BI to create financial reports, I wasn’t sure of the best way to structure my data model. Should I have separate tables for budgets and actuals? Income Statement and Balance Sheet? Line items and transaction headers? Monthly summaries? Reforecasts?
The answer is actually very simple. A single table that contains your Actuals, Budgets, and any Reforecasts for both your Income Statement and your Balance Sheet will be easier to work with than separate tables and your report will perform better too.
This table is a listing of your individual nominal code postings with a minimum of three columns: Date, Account, and Amount. If the table includes Budgets as well as Actuals, then you will need a column to identify for each row, whether it is a budget or actual amount. You may also need a small number of columns with maybe a narrative and reference columns for customers, suppliers, invoices, products, departments/tracking codes, projects, currencies, exchange rates, company and so on. I normally call this table “Journals”, though it may also include Budgets and Reforecasts as “Pseudo Journals”, because it holds the lowest level of posting to the lowest level of account in your data model.
If you follow this approach the starting point for your data model is very simple and may look something like this:
Extended with other tables to control the display of your report and to capture user selections, the ultimate model may look more like this:
Characteristics of the Journals table
- Each row will represent an entry posted to a single Account on a given date. The other entries that make up the complete transaction will be on different rows of the table.
- The Account is the lowest level from your Chart of Accounts that you want to have in your report. In low-volume environments this is likely to be the lowest level in your Chart of Accounts, such as the Nominal Code. In a high-volume environment this may be a higher-level aggregation account.
- The Date is a date column, that represents the effective posting date of the transaction. In a high-volume environment the transactions may be aggregated to a single date in the month, typically, the month-end date. If you are aggregating your transactions by month, don’t be tempted to make this a month column. Keep it as a date, with a relationship to Dates[Date] so that you have a nice one-to-many relationship from “Dates” to “Journals”.
- The Amount is the amount posted to the Account. It has the “mathematically correct” sign based on whether the posting was a debit or a credit and whether the Account belongs to the Income Statement or the Balance Sheet. That is, a sales posting to the Income Statement will normally be negative and the corresponding posting to trade receivables in the Balance Sheet will be positive.
- Additional columns (depending on your data and requirements) may include items like Narrative, Department, Tracking Code, Project, Invoice #, Customer/Supplier ID, Currency, Exchange Rate. Note that if you flatten any header information of the transactions into this “Journals” table, you will most likely also improve the model performance.
Three reasons to include Budgets, Forecasts and Actuals in the same table
- To simplify the data model: If you have an “Actuals” table that has a relationship to “Dates”, “Accounts” and “Departments” then a “Budgets” table or a “Forecasts” table will also need the same relationships. This adds unnecessary complexity to your data model, with three additional relationships needed for each of “Budgets” and “Forecasts”.
- To simplify your DAX measures: Using the one “Journals” table means that the only difference between a calculation for Actuals vs a calculation for Budgets is a filter on the Journals[Source] column, where Journals[Source] is “Actual” or “Budget”. This makes it very simple to use a single DAX measure to populate an “Actual” column and “Budget” column in an Income Statement matrix. It also makes it simpler to implement a disconnected slicer to toggle between say “Budget” and “Prev. Year” as your comparison with Actual, as shown in this video.
- To improve performance of your DAX measures: I have tested the performance of two models that were identical in every respect except that one used the single “Journals” table as described here and the other had three tables of “Actuals”, “Budgets” and “Forecasts”. In both models, there were 40,651 rows of “Actuals”.
The “Journals” model was as shown in the diagram above. The “Actual Budget Forecast” model is shown below and its measures adjusted to take into account the new tables.
You can see that the additional tables for “Budget” and “Forecast” also need relationships with “Accounts” and “Dates”. If there were other dimension tables such as “Departments” or “Customers”, then there would also be additional relationships to these tables as well.
Both reports have an identical page, as shown below that shows actual for the three months to the end of June and the Budget for the remaining nine months.
I did several tests of the refresh time for the matrix visualisation on the page. The “Journals” model took around 3.1 seconds to refresh and “Actual Budget Forecast” model around 3.3 seconds. Additionally, the “Journals” report is marginally smaller at 1,408 KB vs 1,460 KB for the “Actual Budget Forecast” report.
These differences are not massive, but I would expect them to magnify with a larger transaction set. A few million records would not be unusual, instead of the 40,651 records used here.
Tips to optimise the performance of a large ‘Journals’ table
The “Journals” table is likely to be the largest in your model (in terms of the number of rows), so it is good practice to make sure that you take a few steps to optimise its performance. At a high level, these steps are to minimise the number of columns and rows and to minimise the cardinality of each column (the cardinality if a column is the number of unique values contained). Power BI is much more effective at compressing data that have low cardinality.
- Remove any columns that you don’t need.
- Only include history as far back as you need it.
- In Power Query, set the following data types:
a) Journals[Date] to type date (not datetime) to minimise cardinality
b) Journals[Amount] to type currency to minimise cardinality (high cardinality in the Journals[Amount] column can occur where there are rounding issues and you end up with amounts like 30.000000000001).
- If you never want to display the details of an individual posting in the report (even in drill-through), then aggregate the “Journals” table to the lowest-level combination of columns that you need, transforming all dates to the period-end date. This will reduce the number of rows in the table and the cardinality of the columns.
- If there are other date columns in the “Journals” table, such as Journals[DueDate], create an inactive one-to-many relationship from ‘Dates’[Date] to each of these columns. This will prevent Power BI from creating date hierarchies for these columns. It will also enable you to reference these columns in DAX measures using the DAX function USERELATIONSHIP in your DAX measures.
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,...