Daily DAX : Day 468 STARTOFWEEK

DAX Function: STARTOFWEEK

STARTOFWEEK returns a table containing the first date of the week in the current filter context, according to a properly marked calendar table.

Syntax

STARTOFWEEK ( <calendar> )

<calendar> = name of your date/calendar table (must be marked as date table in Power BI)

Return Value

A single-row table with:

  • All primary tagged columns (usually Date)
  • All other time-intelligence related columns from your calendar
Important: This is a time-intelligence function.
→ Requires a proper calendar table marked as Date table
→ Works with calendar (not fiscal) week logic in recent versions

Common Use Cases

  1. Week-to-date (WTD) calculations
    2. WTD Sales = 
CALCULATE(
    [Total Sales],
    DATESBETWEEN(
        'Calendar'[Date],
        STARTOFWEEK('Calendar'),
        LASTDATE('Calendar'[Date])
    )
)
  2. Compare current week vs previous week
    3. Sales same week last year = 
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(
        STARTOFWEEK('Calendar')
    )
)
  3. Show week start date in visuals
    4. Week Commencing = 
FORMAT(
    STARTOFWEEK('Calendar'),
    "ddd dd-mmm-yy"
)
  4. Group fact table rows by week start (in calculated column or measure context)

Quick Reference – Related Functions

Function Purpose
STARTOFWEEKStart of current week
STARTOFMONTHStart of current month
STARTOFYEARStart of current year
ENDOFWEEKEnd of current week (if available in your version)

Last updated pattern: 2025–2026 DAX time-intelligence functions
Most common week start in business = Monday (STARTOFWEEK usually follows ISO / calendar rules)

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 191 MROUND

MROUND DAX Function Syntax: dax MROUND(<number>, <multiple>)     number: The value you want to round.     multiple: The multiple to which you want to round the number. Description: The MROUND function rounds the number to the nearest value that is a multiple of the specified multiple. If the number is exactly halfway between two multiples, it rounds away from zero (e.g., 2.5 to 3 for a multiple of 1). Return Value: A number rounded to the nearest multiple of the specified value. Examples     Basic Rounding:     dax     MROUND(23, 5)         Returns 25, as 25 is the nearest multiple of 5 to 23.     Rounding to Nearest 10:     dax     MROUND(17, 10)         Returns 20, as 20 is the nearest multiple of 10 to 17.     Negative Numbers:     dax     MROUND(-23, 5)         Returns -25, as -25 is the nearest multiple...
Read more

Daily DAX : Day 142 COLLAPSEALL

 The COLLAPSEALL function in Power BI's Data Analysis Expressions (DAX) language is a visual calculation function designed to navigate and manipulate the context of hierarchical data within a visual, such as a matrix or table. Introduced as part of the visual calculations feature in Power BI, it allows users to "collapse" the visual context to the highest level of a specified hierarchy or axis, effectively removing all lower-level details. This function is particularly useful when working with hierarchical data structures (e.g., year > quarter > month) and performing calculations that need to reference aggregated values at the topmost level. Syntax There are two primary forms of the COLLAPSEALL function:     With an Expression (Returns a Value):     COLLAPSEALL(<expression>, <axis>)         <expression>: The calculation or measure you want to evaluate (e.g., [SalesAmount]).         <axis>: The...
Read more