top of page

The 3 things you need to get started with Financial Statements in Power BI

Updated: May 10, 2022

Ledgers, Layout & Link

When starting out with Financial Statements using Power BI, it is best to start off with the 3 fundamentals of Ledgers, Layout and Link.



LEDGERS

This is a table of data containing as a minimum the:

  • Amount

  • General Ledger Code

  • Date(s) - you may have several dates such as transaction date or recognition date; you can even have several recognition dates such as when the recognition date is different between IFRS and US Gaap


Usually this will come from your source system (i.e., SAP) and it often contains other information such as:

  • Currency Code (if your transactions are recorded in multiple currencies)

  • Foreign Keys for any additional dimensions you may want to explain your data by. For instance, you may have a foreign key for "Department" which will link to a "Department" table containing further information about that department such as name or number of employees

  • Transaction time - it's important to split out time from date in your model for performance reasons


If your ledger table consists of Degenerate Dimensions, then I advise you clean this out at the early stages by either by putting them into their native dimensions or Junk Dimension.


LAYOUT

Secondly, it is important to have layouts for each of your financial statements. For instance, the Income Statement will require a layout file that shows all the different levels. Usually you will have a "Level 1" such as Revenue and then a further "Level 2" such as "Operating Revenue" or "Intercompany Revenue".


There is further information needed for the layout such as sort order; this determines which lines come first and can be seen in each of the appropriate videos.


LINK

Finally you need to create a link between the LEDGERS and the LAYOUT.


This is to provide the logic that goes from your chosen layout to the ledgers. Typically this consists of 2 columns of data, the:

  1. General Ledger Code used in the LEDGERS table

  2. Primary Key used in the LAYOUT table


Once you have the above 3 elements, you have the groundwork for creating Financial Statements in Power BI



 

Income Statement


This is the Income Statement as recorded for the London Business Analytics Group. The sound is a little low so make sure it's turned up (fixed in later videos).



 

Balance Sheet


 

Cash Flow








4,108 views0 comments

Commentaires


bottom of page