Daily DAX : Day 172 WEEKDAY

 The WEEKDAY function in Power BI's Data Analysis Expressions (DAX) is a time intelligence function that returns a number representing the day of the week for a given date. This can be particularly useful for analyzing data based on weekly patterns or creating custom calendar-based calculations.

Syntax


WEEKDAY(<date>, <return_type>)


    <date>: A date expression (e.g., a column containing dates or a specific date value).

    <return_type>: An optional integer that specifies the numbering system for the days of the week. If omitted, it defaults to 1.


Return Types

The <return_type> parameter determines how the days of the week are numbered:


    1 (default): Sunday = 1, Monday = 2, ..., Saturday = 7

    2: Monday = 1, Tuesday = 2, ..., Sunday = 7

    3: Monday = 0, Tuesday = 1, ..., Sunday = 6


The function returns an integer between 0 and 7, depending on the <return_type> and the day of the week for the provided date.

Use Case

The WEEKDAY function is commonly used in business intelligence scenarios where you need to:


    Categorize or filter data by weekdays vs. weekends.

    Perform calculations based on the day of the week (e.g., sales analysis by weekday).

    Create custom visualizations or reports that depend on weekly cycles.


Example 1: Basic Usage

Suppose you have a table called Sales with a column OrderDate. You want to determine the day of the week for each order using the default return type (Sunday = 1, Saturday = 7).

DAX


DayOfWeek = WEEKDAY(Sales[OrderDate], 1)


    If OrderDate is April 02, 2025 (a Wednesday), the function returns 4.


Example 2: Identifying Weekends

You can use WEEKDAY to flag weekends (Saturday and Sunday) in your dataset. For instance:

DAX


IsWeekend = 

IF(

    WEEKDAY(Sales[OrderDate], 1) IN {1, 7}, 

    "Weekend", 

    "Weekday"

)


    Here, 1 (Sunday) and 7 (Saturday) are flagged as "Weekend," while other days are "Weekday."


Example 3: Custom Business Logic

Imagine a business that considers Monday as the start of the week (Monday = 1, Sunday = 7). You could use:

DAX


WeekDayNumber = WEEKDAY(Sales[OrderDate], 2)


    For April 02, 2025 (Wednesday), this returns 3.


Practical Application

In a Power BI report, you might use WEEKDAY to:


    Analyze sales trends (e.g., "Do we sell more on Fridays?").

    Filter a dashboard to show only weekday data.

    Combine it with other DAX functions like DATEADD or CALCULATE for more complex time-based analysis.


In summary, the WEEKDAY function is a simple yet powerful tool for breaking down date data into actionable insights based on the day of the week, tailored to your specific calendar needs.

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