How to prepare an income statement with Power BI ?

The increasing usage of Power BI to draft an income statement constitutes a major turning point for accounting and finance professionals. The challenge for the latter consists to use the accounting data to draw meaningful insights. In the era of Big Data, processing large volumes of data has become more mainstream. Power BI is helping to reinforce this trend.

Moving from Excel to Power BI

However, to prepare financial analysis for decision-makers, Excel remains the most widely used tool nowadays. But, from the perspective of transforming large datasets (as with the FEC, the French consolidated file of accounting entries mandatory in case of a tax audit), Excel’s limitations are numerous and significant :

1 million lines maximum
Manual and repetitive tasks
Obligation to learn VBA to automate some processes
Risk of errors
Duplication of work and data
Etc

Power BI allows these limitations to be overcome by writing queries. These can simply be refreshed each time the dataset is updated. Not to mention the advanced functions for data visualisation and report sharing that are not included in Excel. Below are some of the best practices to build a Power BI income statement using large datasets such as the FEC.

Step 1 : transform the data

The first step is to import and transform the data. The goal here is to present the data in the form of tables containing as few columns as possible. If in Excel, we tend to present the data in columns in a single table to use the pivot tables and the VLOOKUP function, the approach must be quite different in Power BI. Indeed, the separation of data into fact tables (accounting daily transactions) and dimension tables (axes of analysis) will optimize the data model. In practice, this will consist of “wrangling” the data so as to keep only the rows and columns essential for the analysis.

Accounting data after transformation in Power Query — After data transformation, only 4 columns left for analysis

Step 2 : build the dimension tables

Then, the task will consist in building a table of dimensions containing the hierarchy of statutory accounts. This step will allow to display the different sections of the financial statements in the appropriate order. Several methods are possible for this. A first method is to add an index column and sort the labels by this column. Which will display the labels in the correct order. This method, if simple, can however quickly reveal its limits when the labels are numerous. Another more advanced and robust method for exploiting large sets of data is to take advantage of the parent / child DAX functions to build the desired hierarchy. On this subject, you can refer to the article written by Marco Russo which explains in detail the use of these functions.

The table containing the accounts hierarchy leveraging the parent / child functions — Parent/child functions leveraged in calculated columns

Then, in order to harness the full power of Power BI time intelligence functions, you will need to build a separate date table. We can do this either in the data view with the DAX functions, or in Power Query using the M language.

The date table built in Power Query using M language — A custom date table built with M functions

Step 3 : link the tables together and build the visualizations

Once the tables constructed, simply link them together in the data model, then write the appropriate measures to calculate the main items of the income statement.

The final model ready to be analyzed — A star schema model compliant with the best standards of data modeling

Once the model finished, you can unleash your creativity to draft the most impactful visualizations for your dashboard !

An example of a visualisation of an income statement thanks to the parent/child functions in DAX — The detailed income statement in a matrix visual with a waterfall alongside

In a nutshell, while building a detailed income statement is far from simple, the task remains feasible under Power BI. Do not hesitate to contact us you need support in producing dashboards from your accounting data !