How to Find the Day of the Week from a Date in Power BI

The Three Primary DAX Formulas for Day Extraction

To effectively categorize and analyze your data based on the timing of events, you can leverage several powerful formulas within DAX. Depending on your desired output—whether for filtering, sorting, or display purposes—you will choose the formula that best suits the need. The following expressions are used to create new calculated columns based on an existing date field, often named 'my_data'[Date] in these examples.

We will explore three distinct methods, offering flexibility in how the day of the week is represented. The first two methods rely on the versatile FORMAT function, which is ideal for linguistic or text-based output, while the third method uses the WEEKDAY function, which is optimized for numerical calculations and standard sorting conventions.

Formula 1: Get Day of Week as Full Name (“Sunday”)

day_of_week = FORMAT('my_data'[Date], "dddd") 

Formula 2: Get Day of Week as Abbreviated Name (“Sun”)

day_of_week = FORMAT('my_data'[Date], "ddd") 

Formula 3: Get Day of Week as Number (Sunday = 1)

day_of_week = WEEKDAY('my_data'[Date]) 

The following comprehensive examples demonstrate how to apply each of these formulas in a practical setting within Power BI Desktop. We will use a standard dataset containing a column of dates, illustrating the resulting calculated column for each scenario:

Method 1: Obtaining the Full Day Name using FORMAT (“dddd”)

When presenting data to stakeholders, the full name of the day (e.g., “Monday” or “Friday”) often provides the highest degree of clarity and is generally preferred for final reports and visualizations. This method relies on the powerful FORMAT function in DAX, which allows you to specify a formatting string to define how a date or number should be displayed. The specific format string used here is "dddd".

The "dddd" format string instructs Power BI to return the full name of the corresponding day based on the locale settings of the report. This ensures that the output is easily readable and removes any ambiguity that might be associated with numerical or abbreviated representations. Using the full name is particularly helpful when creating slicers or labels on charts where space is not a major constraint.

Step-by-Step Implementation for Full Day Name

To implement this solution, you must first navigate to the modeling view in Power BI Desktop where your data table resides. The calculation is performed by adding a new calculated column directly to your existing data table, which we assume is named 'my_data'.

1. In Power BI Desktop, ensure you are in the Data View.

2. Click the Table tools tab at the top of the window. This tab provides options for manipulating the structure and calculations related to your current data table.

3. Click the New column icon. This action opens the formula bar, allowing you to define the new calculated column using DAX.

To get the day of the week as a full name, click the Table tools tab, then click the icon called New column:

Once the formula bar is active, you must type the following expression. Remember to replace 'my_data'[Date] with the actual table and column name containing your date values if they differ from this example.

day_of_week = FORMAT('my_data'[Date], "dddd")

Executing this formula will instantly create a new column named day_of_week that displays the full, spelled-out day for every entry in the Date column. This column is now available for use in any visualizations or further calculations within your Power BI report.

Method 2: Generating the Abbreviated Day Name using FORMAT (“ddd”)

While the full day name offers maximum readability, it can often consume too much space in condensed reports, especially in small table visualizations or narrow chart axes. In such scenarios, using an abbreviated name (e.g., “Mon,” “Tue”) provides a great balance between descriptive clarity and spatial efficiency.

This technique also utilizes the versatile FORMAT function, but it employs a slightly different format string: "ddd". This three-letter specification tells Power BI to return the shortened, localized name of the day. This is a common practice in dashboard design where concise labeling is prioritized.

To initiate the creation of this calculated column, you follow the same procedural steps outlined in Method 1. You must access the Table tools ribbon and select the New column option to open the DAX formula bar.

Then type the following formula into the formula bar, replacing "dddd" with the three-digit format string "ddd":

day_of_week = FORMAT('my_data'[Date], "ddd")

This will create a new column named day_of_week that displays the day of the week as an abbreviated name for the corresponding date in the Date column. This output is ideal for reports where horizontal screen real estate is limited.

Method 3: Calculating the Day Number using the WEEKDAY Function

While text-based day names are useful for visual presentation, numerical representation is often mandatory for sorting, filtering, and performing advanced calculations. If you rely solely on text output (like “Monday” or “Tuesday”), Power BI will sort them alphabetically (e.g., “Friday” then “Monday”), which is analytically incorrect for time series data. The WEEKDAY function addresses this by converting the date into a corresponding number.

The WEEKDAY function is specifically designed to return an integer representing the day of the week for a given date. Unlike the FORMAT function, which returns a text string, WEEKDAY returns a numerical value, making it indispensable for ensuring correct chronological sorting in tables and matrices.

Understanding WEEKDAY Return Types and Syntax

The structure of the WEEKDAY function is straightforward: WEEKDAY(date, [return_type]). The crucial parameter is the optional return_type, which determines which day the week starts on and the numerical range. If the return_type is omitted (as in the formula below), it defaults to 1, meaning the week starts on Sunday (represented by the number 1) and ends on Saturday (represented by the number 7).

• Return Type 1 (Default): Sunday (1) through Saturday (7). This is the standard US format.

• Return Type 2: Monday (1) through Sunday (7). This aligns with the international ISO standard.

• Return Type 3: Monday (0) through Sunday (6). Useful for compatibility with certain legacy systems.

For general reporting purposes, the default (Type 1) or Type 2 are most frequently used. For this example, we utilize the default behavior where Sunday equals 1.

To get the day of the week as a number from 1 to 7, click the Table tools tab, then click the icon called New column:

Then type the following formula into the formula bar. Since we are omitting the optional second argument, the system defaults to the Return Type 1 format (Sunday = 1).

day_of_week = WEEKDAY('my_data'[Date])

This will create a new column named day_of_week that displays the day of the week as a numerical integer for the corresponding date in the Date column. This numeric output is essential if you need to perform calculations based on the day position or require sorting that is chronologically accurate.

Advanced Considerations: Locale and Custom Calendar Tables

It is important to note that when using the FORMAT function, the output text (e.g., “Monday” or “Mon”) is determined by the locale settings of your Power BI Desktop installation and the language settings of the report itself. If your report is shared globally, ensure that the appropriate locale is set to display day names correctly for your end-users.

Furthermore, while using DAX calculated columns is efficient for simple tasks, professional Power BI modeling often requires the construction of a dedicated Calendar table. A separate date table provides a complete continuum of dates, regardless of whether data exists for that specific date, and allows for much more robust time intelligence functions, relationships, and hierarchical drilling down to the day level.

The methods described here are perfect for quick analysis and augmenting existing fact tables. For those ready to dive deeper into time-based calculations, mastering the FORMAT function’s capabilities is crucial. You can find the complete documentation for the FORMAT function in DAX provided by Microsoft.

Further Learning and Power BI Tutorials

Mastering date manipulation is just the first step in leveraging the full power of Power BI. We encourage readers to explore additional tutorials focused on optimizing data models and performing other common transformation and analysis tasks within the platform.

The following tutorials explain how to perform other common tasks in Power BI: