For my session in SQL bits on Financial Modelling in PowerBI there are couple of useful downloads if you want to follow or dissect at home.
This data warehouse has an albeit basic data warehouse version of an accounting table and Account Dimension, so good to use as a demo and starting point.
2) PowerBI P&L Report using “Parent Child” Chart of Accounts
This sample report shows some of the aspects of a basic P&L Statement using a parent/child hierarchy.
- Dealing with Expense and Income signage (Credit/Debit)
- Ragged Hierarchies and Parent/Child
- Pro format layout and sorting
3) PowerBI Report using “Many to Many” Custom Chart of Accounts
This report uses a “custom rules” table to extend the account codes into a reporting ranges and headers in a Many to Many basis.
4) Sample Balance Sheet Report
5) Sample GL Range Rules for “Many to Many” Custom Chart of Accounts
This is sample master data spreadsheet showing Account Ranges and also Rules on how
GL or Account Codes map to those ranges.
Typically when we get “custom rules” from Financial Accountants we get something like this in a spreadsheet that becomes master data.
6) Customised AdventureworksDW2017.bak
Here. we have taken the “out of the box” AdventureworkDW2017 and added the extra “custom rules” table to support a Custom Chart of Accounts.