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:
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:
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
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
Comments