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.
This is a table of data containing as a minimum the:
General Ledger Code
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
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.
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:
General Ledger Code used in the LEDGERS table
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
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).