Creating a set of Financial Statements in Power BI is a common request. Many companies would like to build an Income Statement, Balance Sheet and Cash Flow Statement in Power BI but don’t know where to start. We examine several methods and techniques while highlighting some current limitations of the platform.
This article assumes that you already have some familiarity with the platform. If you’re exploring Power BI as a potential solution for your business, our What is Power BI article provides an overview of the platform.
There are two aspects of building Financial Reports in Power BI that are the most time consuming.
- Importing and Preparing Data for Financial Reports
- Defining and determining which visuals to use
The traditional wisdom is that data prep takes 80% to 90% of the time when building a financial statement.
Planning Your Reports and Dashboards
While it’s true that data prep can be time consuming, many people do not know what they want their final financial statement to look like or which KPI’s they want to highlight.
It helps to know what your desired output will look like when you’re done building the report. Most people already have a set of Financial Statements created in Microsoft Excel that they use as a starting point.
While you develop Power BI Financial Statements reach out to stakeholders and co-workers to begin getting ideas of what they would like to see. Scoping the end result will be time consuming as many people won’t know what they want until they see it.
For ideas of what reports could look like and to see what others are doing, take a look at the Power BI Community Galleries
Financial Statement Automation Using Microsoft Power BI
Microsoft Power BI is part of Microsoft’s Power Platform. The Microsoft ecosystem has everything that most companies need to successfully automate financial statements, KPIs and other financial reports.
The overall process for automation will involve a couple several steps that can vary greatly depending on your organization. No two companies are the same and many use different types of accounting or source transaction software.
Connect to Source Data for Power BI Financial Statements
Before building anything, you have to get data into Power BI. There are really only a few options to bring financial data into Power BI Desktop, the software where Power BI reports are built.
- Export Excel or CSV files from your accounting software.
- Establish a direct connection to your ERP or accounting software.
- Connect to a SQL Database or Data Warehouse.
The source system will dictate the method of establishing the connection. Some software like SAP or Oracle may have specialized connectors. Microsoft Dynamics will let you connect to the backend by SQL or Dataverse, and other systems are in such a niche or so outdated that you may need to use an RPA bot or Power Automate Desktop to automate a manual run process to extract source data.
We explore data extraction and data source automation techniques below.
Exporting Excel or CSV Files from Accounting Software
If you manually export reports such as a chart of accounts, trial balance, or general ledger line-item detail you will want to make sure that the format remains consistent month to the month or period to period.
Power BI prepares data in repeatable steps. If the input format changes from one period to another, the steps Power BI follows will fail causing the report to need to be re-worked.
Here are a few considerations that will save you time and effort later on in the process.
- Power BI can consolidate a folder full of CSV or Excel files.
- Save the Folder in a location that the Power BI Service can locate. Common locations include OneDrive or SharePoint but Power BI will connect to a number of different cloud file storage solutions.
- Files saved on a company specific network folder may require the installation and setup of a Power BI Gateway to enable automatic refreshes. The Gateway allows the Power BI Service to access files that are not hosted on the cloud.
Tip: Excel has row limits and reports can sometimes take a long time to run out of accounting software. You can break the reports into smaller pieces such as a month or a week each time you run it. Try saving each report into a folder and importing that folder into Power BI. Use a naming convention that you can delimit to get the reporting period if transaction dates are not available in the report itself.
Use Microsoft Power Automate to Auto Save Recurring Reports
Some Accounting Software allows you to schedule recurring reports. If yours does, you can setup an automation using Microsoft Power Automate or a similar service to watch your e-mail inbox for a new report and automatically save it to a folder. Power Automate comes with most Microsoft Office 365 licenses and works well with Microsoft Outlook.
If you’re new to Power Automate, you can use Describe it to Design it as a starting point. It allows you to use plain English to describe a process and will suggest an automation flow as a starting point.
Establish a direct connection to your ERP or Accounting Software
Power BI has a number of out of the box connections available for you to bring data in directly from your accounting software. It supports connections from Oracle OCI to QuickBooks Online. The connections require varying degrees of setup, and you may need to enlist the help of your IT department to get these going.
Connecting Power BI to a SQL Database or Data Warehouse.
Another popular way to bring data into your report is through establishing a SQL connection either directly to your accounting software, if supported, or by connecting to a data warehouse if your company already has one. A typical starting point is adding an ODBC connection to Windows for you to work off of, it may later need to be added to the Power BI Gateway to allow for automatic refreshes.
Note: While it’s tempting to direct connect to an ERP to enable live data refreshes at month end to see how the month is shaping up it can have a detrimental impact on performance of the entire accounting system.
How to Setup a Data Model for Financial Statements
The first challenge to setting up a financial statement is getting the data into a format that you can feed into Power BI while retaining the ability to interact with it. Eventually we want to present it in a way that can be drilled into and filtered by the end user.
For this example, we will use a balance sheet that comes in the following format:
The format would be impossible to use as is, because each column is a separate year and month meaning that we would need to add each one as a separate column to our Power BI Matrix Visual.
Your specific format will vary and be more complicated, but these techniques should still be applicable.
Use First Row as Headers Button to Turn Months into Column Names
Promote headers available on the Home tab of the Power Query editor will give us column names to work with in our data model. It will move the first row of your data to become column or field names in the data model.
Best Books to Learn Power BI
From Beginner to Advanced, Power BI books are great ways to learn new techniques and are great reference guides for mastering DAX.
Pop! Automation may receive affiliate commissions for purchases through these links at no additional cost to you.
Unpivot the Data to Structure it in a Way that Can be Visualized
Whenever you’re working with data in Power Bi, the first step should be getting it into a columnar data format. Power BI works best when each column is a specific data type or set of data. Using the Pivot and Un-Pivot features are easy ways to get the data into a format that can be more easily worked with.
This also enables you to split columns such as concatenated year-month into separate columns for year and month that can be used later on in slicers.
We can then add the data back into our Power BI Matrix Visual. The Matrix is by far the most useful visualization method for presenting financial data in Power BI and will get you results the most similar to what you would present in Excel.
Isolating Individual Rows for Calculating Ratios
A drawback of this method is that all of the accounts listed are in a single row. This means that you can’t directly reference them when creating a measure.
For example, if you wanted to calculate a Cash Ratio as Cash divided by Accounts Payable there is no field name where you could easily sum([Cash]) to create a measure.
An easy way to get around this is to use a Calculate Measure that will filter on a specific value. This example sums the [Value] column, and filters it for any time the word “Cash” appears in the column.
Cash = CALCULATE(SUM(Sheet1[Value]), Sheet1[Account] = “Cash”)
Note: Once you setup the formula once, you can copy and paste it many times and only have to update the “Cash” filter for each financial statement line item.
Benefits of Giving Individual Rows a Measure Name
If you setup each row to be it’s own measure, you can then quickly calculate financial ratios. This method may not be ideal for complex financial statements, but it is very easy for future users to see how ratios were calculated. This is a measure dividing two separate measures that were created using the method above.
Cash Ratio = [Cash]/[Accounts Payable]
A secondary benefit is that you can quickly change how numbers are presented. For example, multiply [Accounts Payable] by -1 and it’s now in presentation format.
Switch Values to Rows for Better Financial Formatting
Another step to save yourself a lot of formatting hassles, is going to
Format Visual > Values > Options and click Switch Values to Rows.
This setting makes it much easier to work with a Matrix visual for financial statements. When you add custom measures to the Matrix visual it will by default put them into column format, which is not at all useful when trying to set something up that shows you how calculations were done and to be able to read it from top to bottom.
This is an example of what a matrix visual looks like for the 3 custom measures previously created. As you can see when you click on Values to Rows, it instantly becomes easier to read. The following screenshots are both the same matrix with Cash, Accounts Payable, and Cash Ratio entered in the values field.
How to Format Subtotals in a Power BI Matrix
While it’s a commonly requested feature, the number of ways that you can format text inside of visuals in Power BI are very limited. The most common method to get around this is to add conditional formatting onto a measure where the condition is always met. For example, if a number is above 0% and below 100%
It would look like this when completed:
Because you can’t make an individual row bold or italic, the next best option is to change the background color using conditional formatting for that specific measure.
Use Quick Measures for YTD and Running Totals
Another often overlooked feature in Power BI is the use of Quick Measures. These are mini templates that Microsoft Provides inside of Power BI to generate DAX formulas without the user putting much thought into it.
It’s available under the Home tab of the ribbon, and Quick Measure. There are useful formula builders for things like Running Totals, YTD totals etc.
These become even more powerful when you start to combine them with the methods above. Create one filtered calculation for Accounts Payable for the period amount based on the Value column. Then copy and paste the measure, replacing the Value column with a new Running Total measure.
Use Chat GPT to Make Writing Power BI Measures Easy
Writing Measures and DAX Formulas in Power BI can be a struggle. There are lots of great resources available online, but one of the best is Chat GPT. It’s a natural language processing tool that lets you ask questions, like how do you create a formula in Power BI to generate a year to date total, and the software will generate a suggestion for you.
We broke down many of the use cases for this new technology and how to take advantage of it when working in Power BI: How to Use Chat GPT with Power BI. The technology is currently being integrated with Power BI as a preview feature and will likel go into production soon Quick measure suggestions (experimental preview) – Power BI | Microsoft Learn
You can try out Chat GPT by going to the software maker’s website here: OpenAI
Add KPI Visuals and Cards for Financial Reporting
One of the biggest challenges with Financial Report is to highlight the metrics most important to managers without making them search through pages of financial data.
Power BI was specifically designed with visualizing data in mind. When you create a financial report, they are often paired with callouts of specific metrics or KPIs that managers find the most important.
You can even tailor dashboards to specific people within your organization and automatically filter it based on the role that they have within an Active Directory Group. The screenshot below is an example of a Power BI Card. They also offer a specific KPI visualization that creates a small graph as the background of the visual and will compare performance over time to tell if you are trending towards a metric.
Microsoft has even recently released a KPI specific reporting feature on powerbi.com to make it even faster to view all of your employee’s or department KPIs. With live connections to source data, solutions like this can eliminate the need for manually updating reporting scorecards each month or quarter.
Utilize AI Smart Narratives to Improve Reporting
One of the coolest and most underutilized features of Power BI are Smart Narratives. Microsoft includes several AI powered tools that provide automated insights about your data. This includes converting a graph or chart into words such as highlighting the key drivers as to why sales were down last quarter and automatically creating insights.
Some of the other AI powered tools include Anomaly Detection, Decomposition Trees, and Key Influencer visuals that go beyond traditional tables and charts. These AI insights can add an impressive level of functionality to your dashboards and reports and are included with your existing Power BI licenses, meaning that there’s no need to go out and pay tens of thousands of dollars for specialized tools that provide similar functionality.
Alternatives to Power BI Financial Reporting Using Plugins
There are two major software providers in the Financial Reporting space for Power BI, Acterys and Zebra BI. While they add some interesting new visuals and capabilities we generally avoid them for the following reasons.
- Added Cost – A major benefit of the Power BI Platform is the low price of entry. Third party plugins add an additional layer of cost per report creator or per viewer per month.
- Complexity to Learn Additional Tools – Power BI is relatively easy it can be to learn. Utilizing plugins may make some tasks easier, but there is not as large of a support community as there is with out of the box features.
- Additional License Management – Third party plugins make it relatively easy to add and remove licenses for your users, but it does add a step to add licenses in addition to Power BI pro and premium licenses.
- Non-Transferrable Skillsets – Once you invest time into building financial statements they must be updated as business needs change. It may be difficult to find people with a specialized skillset to manage a specialized plugin.
Financial Statements with Power BI Paginated Reports
Power BI has a number of formatting limitations compared to Excel. It is a fantastic tool at showing smaller subsets of data that can easily be filtered and drilled into, but if you want to re-create an Excel specific format you may want to look into Power BI Paginated Reports which is due for a revamped interface mid to late 2023. The current interface can be challenging to use but allows users to use Power BI datasets and publish reports in a printable format.
Power BI Paginated Reports are built using Power BI Report Builder, a program that is separate from Power BI Desktop and has an interface that’s more similar to SQL Server Reporting Services (SSRS)
Financial Statements with Excel and Power Query
If you already have a Microsoft Excel Financial Statement that you like but are looking for a solution to automatically refresh the data, you may want to explore using Microsoft Power Query’s Get and Transform features to establish a data connection that can easily be refreshed.
Using a Power BI Dataset with Microsoft Excel
Microsoft has recently made it easier to work with Power BI datasets in Excel along with make it easier to export data from Power BI to Excel. If Power BI does not allow you to get the exact formatting that you want, a hybrid solution may be possible.
A major benefit of using a single data set is that it will save you a lot of development time of building out multiple data models and will ensure that the numbers you are presenting match across different reports. Learn more about this feature from Microsoft: Connect Excel to Power BI datasets – Power BI | Microsoft Learn
Conclusion
The biggest challenge with creating financial statements in Power BI is that every company is unique. Different source systems and different reporting requirements make it extremely difficult to have a plug and play financial reporting system. Even with Power BI financial report templates, you would have to work backwards and re-map all of the different measures and field names making it more time consuming than starting from scratch.
If you’re starting to build out financial statements in Power BI we hope you found this information useful, but keep in mind that these are a general guide. If you’re working with highly complex starting data from a wide range of systems, we recommend starting small and building on it over time.
There are many great books about Power BI that will help you get up to speed and make your development process easier.