When it comes to financial reporting in Microsoft Dynamics 365 Business Central, formulas are the secret sauce that can help you transform raw data into meaningful insights. Whether you’re building rows or columns, understanding how to use formulas effectively can make your reports more dynamic, accurate, and insightful.
In this post, we’ll break down the specifics on using formulas in Business Central’s financial reports.
What are financial report formulas?
Formulas in Business Central allow you to calculate values dynamically in your financial reports. They can be used to total accounts, compare periods, or create custom calculations across rows and columns.
How to use formulas
There are different formulas depending on where you are building your report. Below are some of the most common row-specific and column-specific formulas that you’ll need.
Row Formulas
1. Totaling Type Posting Accounts
Use this to pull data directly from the chart of accounts. For example:
- Range of Accounts: 4000..9999
- Selected Accounts: 4000|5000
- Like Account: 4*
2. Totaling Type Formula
Use this to calculate values based on other rows (not GL accounts).
- Example: 1000+1500 calculates the values from rows 1000 and 1500.
Column Formulas
1. Column Type Formulas
Use these to calculate values based on other columns (not GL accounts).
- Calculate Columns: 10+20
- Calculate Range of Columns: 10..30
2. Comparison Date Formulas
These help you compare data across different time periods:
- (blank) — Current period
- -1P — Previous period
- -1FY[1..LP] — Entire previous fiscal year
- -1FY — Current period in previous fiscal year
- -1FY[4..6] — Second quarter of previous year
- -1FY[1..CP] — From the beginning of previous fiscal year to current period in that fiscal year
- -1M — Current period minus one
- -2M — Current period minus two
- -1D — Previous day
3. Comparison Period Formulas
These are must-have formuals if your fiscal periods don’t align with calendar months:
- FY[1] — First period of current fiscal year
- FY[2] — Second period of current fiscal year
Pro Tip: If you’re using dimensions in columns, don’t forget to add a column for blank values. This ensures you capture entries that might be missing a dimension value.
Check out the full series:
- Understanding Financial Reports in Business Central
- All About Row Definitions in Business Central Financial Reports
- All about Column Definitions in Business Central Financial Reports
- Unlocking insights with Business Central’s analysis view
- A practical guide to budgets in Business Central
- How to Include Non-Financial Data in Business Central Financial Reports
