SQL Centre of Excellence

There are some well known design patterns and blogs for handling running sum commonly used on reports such as Profit and Loss Statements.
https://www.daxpatterns.com/cumulative-total/

I’ll show here an alternative approach that I commonly sue when we have a Many to Many table such as:
- A table mapping account codes to Financial Reporting Ranges

- A Date Range table to show YTD, WTD without any DAX

If we take the first example we may have a Profit and Loss Report with four lines on it as below with line 4 being a running sum (Net Invoice Sales)

image

The Rules table that stores mappings from account codes to the reporting ranges and headings may looks something like below for the first three lines of report

image

If we add Line #4 as below we now have a “running sum” without the need for any DAX, the many to many join will do the same function as a running sum.

image

Your mileage may vary and there is nothing wrong with the DAX pattern,
but for some scenarios we may have a M2M table anyway, so we can also leverage it for supporting custom aggregates like running sums..

1) For multiple Reports and Report Variations

As discussed in a previous blog article, simple Profit and Loss may assume a single reporting
hierarchy rolling up account numbers into a reporting line and heading.

As this starts to break down we may have a Many to Many mapping between Account Codes and Reporting Lines. Like below, and for convenience we might to the mapping based on “ranges” of account numbers, even if storage is de-normalised.

As an example we may have three financial reports all using account codes 4130 to 4139 as below. The Standard P&L uses report No 5, but other reports use other definitions and rollups

image

This may then be expanded to a more classical Many to Many Table in a physical Mode

image

And to use this in a Many to Many we follow the ugly looking pattern below with the bridge table highlighted and to make DAX people turn in their graves, a bi directional join to boot!

https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/

image

Once in play we can have many different versions of a financial report, or have many financial reports using account codes in different ways (Eg Cashflow definitions versus P&L definitions)

For financial reporting, can we use the Chart of Accounts as a hierarchy for all our reporting needs ?

The short answer is: In the ideal world - Yes. In the not ideal world – No.

The Chart of Accounts provides us with a way to categorise Account Codes (aka GL Codes) into an account name and and sometimes a heading that can be used for rollup for P&L and BS based reporting.

It’s often portrayed as Parent/Child in sample databases and some smaller accounting packages (QuickBooks loves their hierarchies), but more often than not, its a fixed two level hierarchy above the account code.

Typically we have a hierarchy like this:
-Account Heading. Operating Expenses
-Account Range. Labour Expenses
-Account Code / Name. Account Code 5020 (Bob’s Wages)

So why can’t we use the default chart of accounts for financial reports?. There's a few reasons:

1) This assumes that there is ONE version of the truth and ONE pro forma hierarchy and rollup for Profit and Loss.

For small to mid sized companies that is certainly true, but as we move to larger organisations operating in many countries with many verticals, we end up with variations on the Profit and Loss.

For one division, say price variation might be realised at one line on the report and in another division it may be realised elsewhere.

Definitions like Net Profit may include different items above and below the line, especially as we switch from external focus to internal accounting for say Manufacturing.

So rather than one single Profit and Loss Report, we may need to support multiple Profit
and Loss hierarchies with an Account Code “moving” between sections of the report.

The company may have an accounting review and decide to restructure how account codes rollup, but want the BI platform to show both the old and new style of Profit and Loss Reports.

2) The P&L and BS are not the only Financial Reports

We defintatley have one “Account Name” per “Account Code”, and an account will either by a movement (P&L) or a balance (BS), but when we come to reporting ranges and reporting headers there are other financial report like CashFlow which rolls up account codes from both
P&L and BS into a different type of report showing both movement and balances and then reconciling them.

This gives us a choice. Do we:
- Have a single hierarchy “per report”
- Support multiple hierarchies with the chart of accounts being the “main” one.

I opt for the second option and this then means that one account code needs to map to multiple reporting ranges. Effectively a many to many.

2) The Chart of Accounts doesnt include calculated members.

A financial report will typically have many calculated members which are either
running sums, custom aggregates or alternate rollups.

If we look at example below, the following members are calculated:

- Net Invoice Sales: Running Sum
- Net Sales: Running Sum. Although accountants will explain it as Net Invoice Sales + Adjustments
- EBITA: Running Sum
- Profit Before Tax: Running Sum
- Profit After Tax: Running Sum

  image

To handle these we need a custom hierarchy with these members added in, and the correct sort order to implement running Sums

There are a few ways to implement running Sums.Within DAX the SQLBI folks have it covered here
https://www.sqlbi.com/articles/computing-running-totals-in-dax/

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

image

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.

image


4) Sample Balance Sheet Report

https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/03.%20Balance%20Sheet.pbix

image

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

image

image

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

image

Page List

Page List