How to realize dynamic financial statements with calculated subtotals

by karaoan ‎08-28-2018 04:56 PM - edited ‎08-28-2018 04:57 PM

As I see lots of ongoing questions and misinformed comments ("Power BI can't be used for financial statements") on the topic, I will cover a simple approach how to realize automatically calculating subtotals for financial statements like profit and loss (P/L), balance sheet, cash flow etc. The basis for designing a financial statement report are typically records with the account balances for the required statement e.g. all revenue and expense accounts for the P/L. The subtotal aggregations for the account categories are easy to achieve with an aggregation column e.g. “Software Sales” gets added up in “Total Revenues” as a parent. What’s not so easy is to combine the account groups with subtotals that aggregate these totals into an intermediary result (running totals). For example, the subtotal “Gross Margin” as calculated by “Total Revenues” minus “Cost of Goods Sold (COGS)”. The ideal solution in my view would be a “set” functionality that allows users to specify a group of column elements (accounts) and measures (aggregations, ratios etc.) to be displayed in a certain order. With MDX this is no problem. In DAX it is not available. But there is a way out and it’s not as complicated as some of the other solutions I have seen in the community. So, here’s my “recipe” for a simple 1 level parent child relationship between account and account group/ running total:

Ingredients

1. Properly structured star schema model that looks similar to: The only two tables relevant for our particular example are “VW_GL” the fact/data table with all general ledger transactions and “Dimaccount” with the chart of accounts. 2. Account balances in your fact table/view were all revenue items are using a positive sign and all expenses a negative one unless there is really a debit balance on the revenue account or a credit balance on an expense account. Then it needs to be the opposite. 3. A Report_Structure table.  This can either be a data entry table in Power BI or a table in your data source. Solutions like Acterys enable you -and every user you assign-  to edit and create this table directly from a web browser or Power BI service. This table should have the following structure:

OrderHeaderCalcTypeStatement
0Revenue1PL
1Sales1PL
2DirectCosts1PL
3GrossProfit2PL
4Expense1PL
5Overheads1PL
6Depreciation1PL
7OperatingProfit2PL
8OtherIncome1PL
9NetProfit2PL
10EBIT2PL
11Bank1BS
12Current1BS
13Prepayment1BS
14Fixed1BS
15Inventory1BS
16CurrentLiability1BS
17NonCurrent1BS
18TermLiability1BS
19Liability1BS
20Equity1BS

Important here are the columns. The records will vary depending on your report definition. The records contain your report headers "Header" that will need to match with the account grouping "AccountGroup" in your chart of accounts table. In the columns we also define how to treat them. CalcType specifies if the subtotal is a normal aggregation (“1”) or a calculated running total (“2”) e.g. “Gross Profit”. Statement contains the detail of the respective statement. In this blog I will only cover P&L items with flow measures like Balance Sheet concepts there are additional complexities for example you have to calculate and treat balances properly and not just simply aggregate. Once you have the Report_Structure / Statement_Structure table you need to relate it to you chart of accounts like this:

  1. Add this measure “Header Order” to you Report Structure Table:

Header Order = MAX ( Report_Structure[Order] ) This is necessary to “know” the current order of header. We will need this later.

  1. Add a measure “Filtered” to your chart of accounts table:

Filtered = ISFILTERED ( ChartOfAccounts[Name] ) 6. Build you DAX for “Running Total” and “Report Value”. Report value is the main measure that will act as your value/amount field in the report. In a nutshell it will check if the “Calctype” is “1” a summary of the accounts or with “2” calculate a running total up until that point. As we are using the signs the right way all subtotals of the PL are just a running total of the previous groups. There are two DAX measures involved that look like this:

The Two Key DAX Ingredients

Running Total = IF (     HASONEFILTER ( Report_Structure[Header] ),     CALCULATE (         SUM ( 'GL_Table'[Amount] ),         FILTER (             ALL ( Statement_Structure ), Report_Structure[Header Order] < VALUES ( Report_Structure[Order] )         )     ),     BLANK () ) and Report Value = IF (     AND ( MAX ( Statement_Structure[CalcType] ) = 1, [Filtered] ),     BLANK (),     SWITCH (         MIN ( Statement_Structure[Calctype] ),         BLANK ()BLANK (),         1CALCULATE ( SUM ( 'GL_Table'[All Scenarios] ) ),         2, [Running Total]     ) ) I will leave it at that and encourage to get your "DAX juices flowing" a little, finding out for yourselves what we are doing here.

Voila!

This is the final “cake” with a few embellishments (typical variance calculations, usage of the Acterys Matrix Light for selective drill down, etc): This is a screenshot from a simplified page in the Acterys standard demo for the Xero accounting system. The data model was automatically generated by the Acterys Connector for Xero. Acterys also enables you to enter top down/bottom up numbers in the table for planning and forecasting processes. The sample can be downloaded here: PL Sample For further questions please contact me.