BC.CLOSINGBALANCE

Overview

The BC.CLOSINGBALANCE function returns the closing (ending) balance of a GL account (at the end of the specified reporting period or date).

This function requires the AL Language extension for Microsoft Dynamics 365 Business Central

To return the opening balance of a GL account, use the BC.OPENINGBALANCE function.

Syntax

=BC.CLOSINGBALANCE(
    ConnectionName,
    Account,
    AccountCategory,
    AccountSubcategory,
    AsOf,
    Dimensions,
    UseReportingCurrency
)

Arguments

Argument	Required/Optional	Description
`ConnectionName`	Optional	Provide one of the following values: Connection name as defined in Connection Manager. Velixo range expression defining a set of connection names. An array of settings as described in the Multiple connections per formula article. We recommend using the VX.SETTINGS function to construct the array. OR Omit the argument to return results for all compatible connections with default aggregation settings.
`Account`	Optional if `AccountCategory` or `AccountSubcategory` is specified	The code of the general ledger account, a spill range, or a cell range containing account codes. For example, `10100`, `A1:A10`, or `A1#` Supports Velixo range expressions for specifying multiple values.
`AccountCategory`	Optional if `Account` or `AccountSubcategory` is specified	One or more account category names, visible in the Business Central Chart of Accounts. The following values are accepted: `Assets`, `Liabilities`, `Equity`, `Income`, `Cost of Goods Sold`, `Expense` Supports Velixo range expressions for specifying multiple values.
`AccountSubcategory`	Optional if `Account` or `AccountCategory` is specified	One or more account subcategory names. Use the BC.EXPANDACCOUNTSUBCATEGORYRANGE function to retrieve available subaccount names. Supports Velixo range expressions for specifying multiple values.
`AsOf`	Required	Balances will be calculated as of this date
`Dimensions`	Optional	A two-column Excel range where the first column contains the name of a dimension and the second column contains a semicolon-separated list of values for that dimension You can specify dimension values using ranges, wildcards, and other techniques. You can also use the VX.SETTINGS function to create the dimension array. See the Using Dimensions article for more details.
`UseReportingCurrency`	Optional	When a reporting currency is configured for your organization, set this argument to `TRUE` to return the results using the configured currency. Accepted values: `TRUE` or `FALSE` Default value: `FALSE`

Examples

Closing balance for a single account

=BC.CLOSINGBALANCE(
    "BC",
    "11110",
    ,
    ,
    "2025-1-31"
)

Description: Returns the closing balance of the account “11110” at the end of January 2025.

Result:

Closing balance in specified dimensions

=BC.CLOSINGBALANCE(
  "BC",
  "11110",
  ,
  ,
  "2025-01-31",
  {"DEPARTMENT","SALES";"AREA","*"}

Description: Returns the closing balance for results tagged as dimension value Department - Sales, and all Area dimension values except empty ones in the account “11110” at the end of January 2025.

Result: