Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: Calculated column Calculated table Measure Visual calculation
Note
This function is discouraged for use in visual calculations as it likely returns meaningless results.
Evaluates the expression
at the date corresponding to the end of the previous year in the current context.
OPENINGBALANCEYEAR(<expression>,<dates>[,<filter>][,<year_end_date>])
Term | Definition |
---|---|
expression |
An expression that returns a scalar value. |
dates |
A column that contains dates. |
filter |
(optional) An expression that specifies a filter to apply to the current context. |
year_end_date |
(optional) A literal string with a date that defines the year-end date. The default is December 31. |
A scalar value that represents the expression
evaluated at the first date of the year in the current context.
The dates
argument can be any of the following:
Constraints on Boolean expressions are described in the topic, CALCULATE function.
The filter
expression has restrictions described in the topic, CALCULATE function.
The year_end_date
parameter is a string literal of a date, in the same locale as the locale of the client where the workbook was created. The year portion of the date is ignored.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
The following sample formula creates a measure that calculates the 'Year Start Inventory Value' of the product inventory.
= OPENINGBALANCEYEAR(SUMX(ProductInventory,ProductInventory[UnitCost]*ProductInventory[UnitsBalance]),DateTime[DateKey])
OPENINGBALANCEQUARTER function OPENINGBALANCEMONTH function Time intelligence functions CLOSINGBALANCEYEAR function
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register today