[Bug]: Trial Balance (Excel): Starting Balance calculated incorrectly

[miljance]

Describe the issue

The Trial Balance (Excel) report contains a bug where the Starting Balance is calculated incorrectly in two distinct scenarios.

The root cause is in this logic:

GLEntry.SetFilter("Posting Date", GLAccount.GetFilter("Date Filter"));
if GLEntry.FindFirst() then begin
    GLAccount2.SetFilter("Date Filter", '..%1', GLEntry."Posting Date" - 1);
    GLAccount2.CalcFields("Balance at Date", "Add.-Currency Balance at Date");

Bug 1 — Income Statement accounts show non-zero Starting Balance instead of 0

At the end of each fiscal year, Business Central automatically closes all Income Statement accounts (revenues and expenses) by posting closing entries that bring their balance to exactly zero. This is standard accounting behavior — P&L accounts do not carry a balance forward into the new year.

When running the Trial Balance (Excel) report for a period such as 01.01.2025..12.31.2025, the Starting Balance column for Income Statement accounts should therefore always be 0.

However, due to a bug in how the report calculates the Starting Balance, those year-end closing entries are not taken into account. As a result, the report incorrectly shows a non-zero Starting Balance for Income Statement accounts — displaying the net movement from the prior year instead of zero. This makes the report misleading and inconsistent with what a consultant or auditor would expect to see.

---

Bug 2 — Wrong Starting Balance when the first posting in the period is not on the first day of the period

When calculating the Starting Balance, the report looks for the first General Ledger entry within the requested date range and uses its posting date to determine the cutoff point for the Starting Balance calculation.

This approach produces incorrect results whenever the first posting in the period does not fall on the very first day of the period. For example, if the date filter is 01.01.2025..12.31.2025 but the earliest posting on a given account happens to be on 03.01.2025, the report calculates the Starting Balance as of 02.01.2025 — which means it incorrectly includes entries posted on 01.01.2025 and 02.01.2025 in the Starting Balance, even though those dates fall within the selected reporting period.

The Starting Balance should always reflect the account balance strictly before the start of the requested period, regardless of when the first actual posting within that period occurred.

Expected behavior

For a date filter of 01.01.2025..31.12.2025:

• Income Statement accounts should show Starting Balance = 0, because the closing entry posted on C31.12.2024 zeroes out the account. The current code misses this entry and leaks the prior year net movement into the Starting Balance.
• All accounts should have their Starting Balance calculated relative to the start of the requested date filter period, regardless of when the first GL Entry within that period was actually posted.

Steps to reproduce

Bug 1 — Income Statement accounts

1. Ensure at least one full closed fiscal year exists (e.g., 2024) with postings on Income Statement accounts.
2. Run the Trial Balance (Excel) report with date filter 01.01.2025..31.12.2025.
3. Open the generated Excel file and inspect the Starting Balance column for any Income Statement account (revenue or expense) that had activity in 2024.

Actual: Starting Balance = net movement from 2024 (non-zero)
Expected: Starting Balance = 0 (closing entry on C31.12.2024 should bring balance to zero)

Bug 2 — Wrong date boundary

1. Create a new G/L Account (e.g., 9999TEST).
2. Post a General Journal line to 9999TEST with Posting Date = 03.01.2028 (use any offset account). Intentionally skip 01.01 and 02.01.
3. Post a second General Journal line to 9999TEST with Posting Date = 01.01.2028.
4. Run the Trial Balance (Excel) report filtered to account 9999TEST and date range 01.01.2028..31.12.2028.
5. Inspect the Starting Balance column for account 9999TEST.

Actual: Starting Balance includes the value from the entry posted on 01.01.2028, because FindFirst returns the entry on 03.01.2028, and 03.01.028 - 1 = 02.01.2028, making the filter ..02.01.2028 which incorrectly covers 01.01.2028.
Expected: Starting Balance = 0 — no entries exist before 01.01.2028 and the period start date itself must never be included in the Starting Balance.

Additional context

No response

I will provide a fix for a bug

• I will provide a fix for a bug

[miljance]

@mynjj
Created after closing the issue in the wrong repo:
microsoft/ALAppExtensions#29838

The fix could either simple with two lines of code which includes:

            GLEntry.SetCurrentKey("Posting Date");
            GLEntry.SetFilter("Posting Date", GLAccount.GetFilter("Date Filter"));
            if GLEntry.FindFirst() then begin
                GLAccount2.Copy(GLAccount);
                GLAccount2.SetFilter("Date Filter", '..%1', ClosingDate(GLEntry."Posting Date" - 1));
                GLAccount2.CalcFields("Balance at Date", "Add.-Currency Balance at Date");

or a bit more complex so that we get rid of this a bit autistic part of the code that I created back then.