BC.TURNOVER | Velixo

Overview

The BC.TURNOVER function returns the balance turnover of one or more GL accounts between two dates.

This function requires the Velixo for Business Central extension to be installed in your Business Central environment.

Business Central creates year-end closing entries when you close a fiscal year. These entries are excluded from the result by default. Set IncludeClosingEntries to TRUE to include them, for example to report on the closing activity itself, or to calculate retained earnings the way the Business Central UI does.

This feature supports Drilldown – a functionality that lets you list all underlying transactions for a formula.

Syntax

=BC.TURNOVER(
    ConnectionName,
    Account,
    AccountCategory,
    AccountSubcategory,
    FromDate,
    ToDate,
    Dimensions,
    UseReportingCurrency,
    IncludeClosingEntries
)

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#`
`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 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 values.
`FromDate`	Required	The inclusive start date in YYYY-MM-DD format or a cell reference to a date
`ToDate`	Required	The inclusive end date in YYYY-MM-DD format or a cell reference to a 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 The dimension values can be specified 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`
`IncludeClosingEntries`	Optional	If `TRUE`, the year-end closing entries are included in the calculation. If `FALSE`, closing entries are removed from the returned amount. Accepted values: `TRUE` or `FALSE` Default value: `FALSE` Specifying this argument requires the Velixo for Business Central extension version 2026.7 or later. This applies to `FALSE` as well as `TRUE`. Omitting the argument, or pointing it at an empty cell, does not require the update.

Examples

Turnover for a single account

=BC.TURNOVER(
    "BC",
    "11110",
    ,
    ,
    "2024-1-1",
    "2024-3-31"
)

Description: Returns the turnover for the account “11110” in Q1 2024.

Result:

Excel worksheet showing BC.TURNOVER returning the turnover for account 11110 in Q1 2024 in Business Central

Turnover for a range of accounts

=BC.TURNOVER(
    "BC",
    D1:D10,
    ,
    ,
    "2024-1-1",
    "2024-3-31"
)

Description: Returns the turnover for the account numbers in cells in the range from D1 to D10 in Q1 2024.

Result:

Excel worksheet showing BC.TURNOVER returning the turnover for a range of accounts from cells D1 to D10 in Q1 2024

Turnover for accounts in a spill range

=BC.TURNOVER(
    "BC",
    D1#,
    ,
    ,
    "2024-1-1",
    "2024-3-31"
)

Description: Returns the turnover for accounts listed in the spill range beginning with cell D1 in Q1 2024.

Result:

Excel worksheet showing BC.TURNOVER returning the turnover for accounts in a spill range starting at cell D1 in Q1 2024

Turnover for specified dimensions

=BC.TURNOVER(
  "BC",
  "11110",
  ,
  ,
  "2024-01-1",
  "2024-01-31",
  {"DEPARTMENT","SALES";"AREA","*"})

Description: Returns the turnover for results tagged as dimension value Department - Sales, and all Area dimension values except empty ones in the account “11110” in January 2024.

Result:

Excel worksheet showing BC.TURNOVER with dimension filter returning turnover for account 11110 filtered by Department Sales and all Area dimensions in January 2024