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.
1) AdventureWorksDW2017
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.
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak
2) PowerBI P&L Report using “Parent Child” Chart of Accounts
https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/01_P%26L_ParentChild.pbix
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
https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/02_P%26L_CustomM2M.pbix
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
https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/03.%20Balance%20Sheet.pbix
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.
https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/glRules.xlsx
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.
https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/AdventureWorksDW2017.bak
Hello Bob,
Maybe it’s a silly question, but how would you calculate a value for GL Amount in a previous year? I have tried using calculate and sameperiodlastyear, but I am getting wrong results.. thank you for your support!
Hi Alex, The SQLBI folks have a great blog on this all things period related.
https://www.daxpatterns.com/custom-time-related-calculations/
I often use DAX like below. assuming its a business calendar and not a Jan-Dec one.
VAR pp=CALCULATE(CALCULATE(LASTNONBLANK(‘Date'[Fiscal Period],1)), filter(all(‘Date’), ‘Date'[Fiscal Period] < max('Date'[Fiscal Period]))) RETURN IF ( HASONEVALUE ( 'Date'[Fiscal Period]), CALCULATE ( [GL Amount] , FILTER ( ALL ( Date ), 'Date'[Fiscal Period] = pp ) ), BLANK () )