Using Many to Many for Financial Reporting

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

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

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

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)

Leave a Reply