Financial Statements Dax Measures

Hi mohsinmasood ,

First of all, may I ask you if your company uses ERP?  I would have thought that standard ERP system can generate such standard financial statements, like BS and PL.  Of course ERP generated financial statements tend to be just basic ones, and Power BI can give much more flexibly regarding the visualizations.  Also getting ERP helpdesk (like SAP) to make modifications to suit your requirements can be super slow and can take months and years to get things done while comparable things can be done by Power BI in a fraction of the time if we have the access. 😂

As you know, while the BS is the accumulation of all the transactions for the BS GL since the inception, PL is just for transactions for the year, and the reserve is accumulating the PL GL amounts in one GL since the inception up to the end of the previous fiscal year.  With this logic in mind, it is definitely doable to generate financial statements from the raw transaction data using Power BI.  But companies I've seen have ERP in place, so I never had to do this from scratch from the transaction data 🙂.  

My observation on your switch formula is that, instead of writing each account grouping in the formula, mapping all these accounts as "BS" or "PL" in the chart of accounts mapping will make things simpler, and prevents you from manually writing additional GL accounts in the formula if a new GL is added in your CoA.  Mapping is the key to the automation of the financial reporting, and I would create a mapping like below, and write a formula to say that if it is BS account, accumulate the transaction since the inception, while it is PL, just accumulate since the beginning of the year, and for the previous year PL transactions transfer to the retained earnings equity GL which is mapped to Equity line.  

BS will have Assets, Liabilities, and Equity as FS category, while PL will have Revenue and Expense as FS category.  FYI, I assumed that the Amortization and Depreciation shown in the table below are from the PL side instead of the BS side because they do not say "Accumulated Depreciation" or "Accumulated Amortization", etc. 

Best regards,