How to Use Comparison Period and Date Formulas in Business Central Financial Reporting?

What Are Financial Reports? (Formerly Account Schedules)

Account Schedules were traditionally used in Business Central to create custom financial reports. They allowed users to define the layout of financial statements by specifying rows and columns, and to perform calculations that aren’t possible directly in the chart of accounts. This feature was particularly useful for creating subtotals, profit margins, and other financial metrics. Recently, the functionality of Accounts Schedules has been renamed and enhanced as Financial Reports.

Using Comparison Period and Date Formulas

What Are Comparison Period and Date Formulas?

In Business Central’s financial reporting capabilities, one of the most powerful features is the ability to create dynamic date comparisons using Financial Reports. Whether you need to analyse year-over-year performance, track monthly trends, or create custom period comparisons, understanding how to use comparison period formulas is essential for effective financial analysis.

Understanding Date Formulas in Business Central

Before diving into specific formulas, it’s important to note that these formulas work with both actual (G/L Entries) and budgeted (G/L Budget Entries) figures. While our examples assume a calendar fiscal year for simplicity, these formulas are adaptable to any fiscal year structure.

1. Current Year Month-wise Analysis

For Income Statement

Using Comparison Period Formula

Column No.	Column Header	Column type	Ledger Entry Type	Formula	Comparison Period Formula
C10	January	Net Change	Entries		FY[1]
C20	February	Net Change	Entries		FY[2]
C30	March	Net Change	Entries		FY[3]
C40	April	Net Change	Entries		FY[4]
C50	May	Net Change	Entries		FY[5]
C60	June	Net Change	Entries		FY[6]
C70	July	Net Change	Entries		FY[7]
C80	August	Net Change	Entries		FY[8]
C90	September	Net Change	Entries		FY[9]
C100	October	Net Change	Entries		FY[10]
C110	November	Net Change	Entries		FY[11]
C120	December	Net Change	Entries		FY[12]
C130	Total	Formula	Entries	C10..C120

Income Statement using Comparison Period Formula

Using Comparison Date Formula

Column No.	Column Header	Column type	Ledger Entry Type	Formula	Comparison Period Formula	Comparison Date Formula
C10	January	Net Change	Entries			-11M
C20	February	Net Change	Entries			-10M
C30	March	Net Change	Entries			-9M
C40	April	Net Change	Entries			-8M
C50	May	Net Change	Entries			-7M
C60	June	Net Change	Entries			-6M
C70	July	Net Change	Entries			-5M
C80	August	Net Change	Entries			-4M
C90	September	Net Change	Entries			-3M
C100	October	Net Change	Entries			-2M
C110	November	Net Change	Entries			-1M
C120	December	Net Change	Entries
C130	Total	Formula	Entries	C10..C120

Income Statement using Comparison Date Formula

For Balance Sheet

Using Comparison Period Formula

Column No.	Column Header	Column type	Ledger Entry Type	Formula	Comparison Period Formula
C10	January	Balance at Date	Entries		FY[1]
C20	February	Balance at Date	Entries		FY[2]
C30	March	Balance at Date	Entries		FY[3]
C40	April	Balance at Date	Entries		FY[4]
C50	May	Balance at Date	Entries		FY[5]
C60	June	Balance at Date	Entries		FY[6]
C70	July	Balance at Date	Entries		FY[7]
C80	August	Balance at Date	Entries		FY[8]
C90	September	Balance at Date	Entries		FY[9]
C100	October	Balance at Date	Entries		FY[10]
C110	November	Balance at Date	Entries		FY[11]
C120	December	Balance at Date	Entries		FY[12]
C130	Total	Formula	Entries	C10..C120

Balance Sheet using Comparison Period Formula

Using Comparison Date Formula

Column No.	Column Header	Column type	Ledger Entry Type	Formula	Comparison Period Formula	Comparison Date Formula
C10	January	Balance at Date	Entries			CY-11M
C20	February	Balance at Date	Entries			CY-10M
C30	March	Balance at Date	Entries			CY-9M
C40	April	Balance at Date	Entries			CY-8M
C50	May	Balance at Date	Entries			CY-7M
C60	June	Balance at Date	Entries			CY-6M
C70	July	Balance at Date	Entries			CY-5M
C80	August	Balance at Date	Entries			CY-4M
C90	September	Balance at Date	Entries			CY-3M
C100	October	Balance at Date	Entries			CY-2M
C110	November	Balance at Date	Entries			CY-1M
C120	December	Balance at Date	Entries
C130	Total	Formula	Entries	C10..C120

Balance Sheet using Comparison Date Formula

2. Previous Year vs Current Year Comparison

Column Setup

Column No.	Column Header	Column type	Ledger Entry Type	Formula	Comparison Period Formula
PY10	Previous Year	Net Change	Entries		-1FY
CY10	Current Year	Net Change	Entries

Previous Year vs Current Year Comparison of Income Statement

Column No.	Column Header	Column type	Ledger Entry Type	Formula	Comparison Period Formula
PY10	Previous Year	Balance at Date	Entries		-1FY
CY10	Current Year	Balance at Date	Entries

Previous Year vs Current Year Comparison using Period Formula for Balance Sheet

Column No.	Column Header	Column type	Ledger Entry Type	Formula	Comparison Period Formula	Comparison Date Formula
PY10	Previous Year	Balance at Date	Entries			CM-1Y
CY10	Current Year	Balance at Date	Entries

Previous Year vs Current Year Comparison using Date Formula for Balance Sheet

3. Quarterly Period

Column No.	Column Header	Column type	Ledger Entry Type	Formula	Comparison Period Formula
1	Q1	Net Change	Entries		FY[1..3]
2	Q2	Net Change	Entries		FY[4..6]
3	Q3	Net Change	Entries		FY[7..9]
4	Q4	Net Change	Entries		FY[10..12]
5	Total	Formula	Entries	1..4

Quarterly Period Comparison using Period Formula

Best Practices for Financial Reporting in Business Central

For balance sheet accounts, use “Balance at Date” formulas to ensure accurate point-in-time reporting
For income statement accounts, utilize “Net Change” formulas to capture period activity

By effectively using comparison period and date formulas in Business Central financial reporting, businesses can gain deeper insights into their financial health. Understanding these formulas allows for better trend analysis, performance tracking, and strategic decision-making.

Need help setting up financial reports in Business Central? Contact us for expert assistance!

Credit: Sri Divya Sivani and Business Central Practice Team

Get in Touch With Us

Fill out the form below and we’ll get back to you as soon as possible!

Please leave this field empty.

Cetas will use the contact information you provide to reach out about our products and services. You can unsubscribe at any time. Learn more about our privacy policy.