Daily DAX : Day 448 CLOSINGBALANCEWEEK

CLOSINGBALANCEWEEK DAX Function in Power BI

Description

The CLOSINGBALANCEWEEK function is a time intelligence function in DAX (Data Analysis Expressions) used in Power BI. It evaluates a given expression at the last date of the current week in the context, returning the "closing balance" for that week.

This function was introduced in 2025 as part of the new calendar-based time intelligence features, which provide better support for week-based calculations.

Syntax

CLOSINGBALANCEWEEK ( <Expression>, <Calendar> [, <Filter>] )
  • <Expression>: The expression to evaluate (e.g., a measure like SUM of sales or inventory balance).
  • <Calendar>: A reference to a calendar table or column (required in the new calendar-based functions).
  • <Filter>: Optional filter expression (rarely used).

How It Works

In a report filtered to certain dates (e.g., a specific day or multiple days within a week), this function ignores the exact date filter and always computes the expression as of the end of the week containing the current context dates.

This is particularly useful for semi-additive measures where you don't sum values over time but take a snapshot at period end.

Use Cases

  • Inventory or Stock Balance: Show the inventory level at the end of each week, regardless of the selected day.
  • Account Balances: Display bank account or financial balances at week-end.
  • Week-to-Date (WTD) Closing Values: Combine with other functions like TOTALWTD for week-based reporting in fiscal calendars that are week-aligned.
  • Retail or Operations Reporting: When business weeks don't align with standard months (e.g., ISO weeks or custom fiscal weeks).

Example

Assuming a measure for current inventory:

Weekly Closing Inventory = 
CLOSINGBALANCEWEEK (
    SUM ( Inventory[StockLevel] ),
    'Date'[Date]  // or your Calendar table
)

In a table visual grouped by dates, this measure will show the same stock level for all days in a week — the value from the last day of that week.

Notes

  • Requires a properly marked Date/Calendar table.
  • Part of enhanced week-based time intelligence (e.g., pairs with OPENINGBALANCEWEEK, TOTALWTD).
  • Enable the preview feature in Power BI if using early versions post-2025 introduction.


Comments

Post a Comment

Popular posts from this blog

Daily DAX : Day 131 SELECTEDMEASURE

 The SELECTEDMEASURE function in Power BI's DAX (Data Analysis Expressions) language is a powerful tool used primarily within calculation groups to dynamically reference the measure currently being evaluated in a report. It’s part of the calculation group feature introduced to streamline complex reporting scenarios and improve maintainability by reducing the need to duplicate measure logic. What is SELECTEDMEASURE? SELECTEDMEASURE is a DAX function that returns a reference to the measure that is currently active or "selected" in the context of a calculation group. It doesn’t take any arguments and is typically used in the expression of a calculation item within a calculation group. This allows you to write generic logic that can dynamically apply to whichever measure is being evaluated in a visual, without hardcoding specific measure names. Syntax SELECTEDMEASURE()     Returns: A reference to the measure currently being evaluated in the report context. Use Case SELECTEDME...
Read more

Daily DAX : Day 446 INFO.CSDLMETADATA

Power BI DAX Function: INFO.CSDLMETADATA INFO.CSDLMETADATA` is a specialized DAX function introduced as part of a broader set of "INFO" functions (like `INFO.TABLES` or `INFO.MEASURES`) used for **model introspection**. It allows you to programmatically access the underlying structure of your Power BI semantic model. What does it do? The function returns a table with a single column containing the Conceptual Schema Definition Language (CSDL)  metadata of your model in XML format . In plain English, it exports a highly technical "blueprint" of your entire data model—including tables, columns, relationships, and measures—as a structured XML string. It is essentially the DAX equivalent of the `DISCOVER_CSDL_METADATA` Dynamic Management View (DMV) used in Analysis Services. --- Syntax and Output The syntax is straightforward as it requires no arguments: ```dax EVALUATE INFO.CSDLMETADATA() ``` Return Value : A table with one column and one row. Content : A long string ...
Read more

Daily DAX : Day 453 ENDOFWEEK

Power BI DAX Function: ENDOFWEEK Description The ENDOFWEEK function is a Time Intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns the date that represents the end of the week containing a specified date (typically Saturday, as weeks are considered to start on Sunday by default in DAX time intelligence). Syntax ENDOFWEEK( [, ]) : A column reference (usually from a Date table) or a date expression. Required. : Optional. Specifies the week-ending convention. Default is Saturday if omitted. Examples 1. Basic usage (default: week ends on Saturday): End of Week = ENDOFWEEK('Date'[Date]) For a date like January 8, 2026 (Thursday), this would return January 10, 2026 (Saturday). 2. In a measure for cumulative sales up to end of week: Total Sales to End of Week = CALCULATE( SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], ST...
Read more