Here comes some long awaited comfort functions for part 2 of my easy P&L series. In the first section I’ve presented the general principle on how to work with a structure using an accounts-group-table. Today I will present 2 alternatives to define the reports without specifying single accounts. So if a new accounts are added to the chart of accounts, you don’t have to adjust your report definitions: Just make sure that to fill in all the fields in your account-group-table and you’re ready to go 🙂
No need to specify single accounts
So you only need to adjust your report definitions if you add new group items. If that’s still too much, take the 2nd solution, which will even eliminate that requirement:
- Individual Account Layout: Just define each subtotal and determine for which subtotals single accounts shall be shown
How to use it:
2. Ultrashort Account Layout: Further simplification of just defining the groups (hierarchy) that shall be shown (with option to filter on one of them)
How to use it:
So these 2 different layouts will both produce the same reports incl. all accounts – just like in the first example. So you can choose which layout-style suits you best – actually, you can use all 3 in parallel. You just have to make sure to grab your pivot-rows from the correct tables and in Excel to grab the matching measures, as they all have their own bridge-tables (which need to be used in the measures):
How it works
General principle is to use the account-groups-table as a lookup-table for the single accounts that belong to each group. And as we want to re-use the transformation-logic to the bridge-table, we aim for “just” transforming these shortened report definition-tables into the form of the “original” report definitions, where accounts, subtotals and everything else is on its right place already.
Simple as this sounds, the actual steps are quite extensive and I will not go into details here, as you don’t need to edit the code if you use the attached files. Just swap the existing import-tables to your data-sources. The only connection to my logic which you have to take care about is the accounts-table, which should be named “DimAccount” and its key-column should be named “AccountKey” (rename in the query-editor, if the name in your source is different).
Key for the magic transformation is an account-group-table (DimAccountGroups) where every group has an adjacent sort-column, defining the sort-order of which the group-items will be sorted in the reports. This column will be used as a key-column for our transformations. These sort-columns all need a prefix (like “Key*” ) in order to be identified from the query-logic. (If you already have a account-group-table that you want to use and cannot rename the columns, create an intermediate query where you rename the columns accordingly before feeding into the functions).
Besides the group-pair-columns in your account-group-table there are some columns for the individual accounts (see the image below). If your existing columns have different names, you need to fill them into the “MyValues”-column. If you have more columns that you want to maintain, just fill their names into the “MyValues”-column as well. Otherwise the automatic transformation will not work:
Same goes for the “Report-definition”-tables:
If you should experience performance problems from the bridge-table getting too long: Just filter the import of your report-table down to those reports that are actually needed in your current file.
These translation tables are just for your convienence to adapt this model to your business solution. If you understand how to adjust the code, you can just edit the code and delete all these translation tables.
In the attached files, the sample data is already attached in the file. So you can instantly play around with it and follow the steps in the query-editor easily.P&L Comfort Functions PBIX
P&L Comfort Functions XLSX
I will share the extensive M-code on Github as soon as I managed this awe… resource …
Also check out the follow-up-post, where I’m sharing an easy way to sign-switching/unary operators (!!)
Enjoy & stay queryious 🙂