Formulas in Dynamics 365 Business Central Account Schedules are the hardest concept for most users to grasp, because their use is just not intuitive. There are a few basic concepts that can help get you started.
When getting started with a row setup, you need to choose either Posting Accounts or Formula in the Totaling Type column. These two options indicate whether you will be pulling numbers from general ledger accounts (posting accounts) or calculating against other rows (formula). There is another option Total Accounts that lets you pull numbers from total accounts from your chart of accounts instead if you are using total accounts instead of or in addition to posting accounts. Once you have defined your option in the Totaling Type column, then you need to populate the Totaling column with either general ledger accounts or formulas, depending on what you have chosen.
Getting started with formulas in Business Central Account Schedules
If you are a dimension user, you need to expand your selections to specific columns available in the row setup for dimensions. These are Dimension Totaling columns. The two global dimensions will always be available for use, but any other dimensions will appear with a label that is Dimension 3 Totaling or Dimension 4 Totaling (etc.) until you define which specific dimensions you want account schedules to use by defining them in analysis views. By filtering on dimensions in these columns, you can report at a deeper level and break down the segments of general ledger accounts to their dimension components in your financial reporting.
Department Code as an example of Dimension Totaling on a row
When getting started with a column layout, you need to choose either a definition of time like Net Change or Balance at Date or Formula in the Column Type column. These two options indicate whether you will be pulling numbers from general ledger accounts (posting accounts) or calculating against other columns (formula). If you have chosen Formula as your Column Type, you need to fill in the formula. The same options for Dimension Totaling are available here, just as they are for the row setup.
Customergroup Code as an example of Dimension Totaling on a column
All the rest of the information you need about account schedule formulas happens in the Comparison Period Formula and Comparison Date Formula columns. There’s so much information there, we need to address it in a post of it’s own. Stay tuned, it’s coming up next!
This posting is part of the Dynamics 365 Business Central Account Schedules 101 series. Find the entire list of lessons here.