How to Get Weekday Name in Power BI

Analyzing your data by the day of the week can reveal powerful patterns you’d otherwise miss - like which days your marketing emails get the best open rates or when your B2B website traffic peaks. This guide will walk you through the simple DAX formulas you need in Power BI to turn a standard date column into a weekday name and number, helping you unlock deeper insights from your company’s data.

Why Does Weekday Analysis Matter?

Before jumping into the “how,” it’s worth understanding the “why.” Segmenting performance by weekday can expose critical business rhythms. For many companies, data isn't a flat line, it ebbs and flows with the weekly routines of their customers.

Here are a few practical examples:

• E-commerce Stores: Shoppers might browse during their weekday lunch breaks but make most of their purchases over the weekend. Seeing this pattern can help you schedule sales promotions and social media ads more effectively.
• SaaS Companies: B2B companies often see website traffic, demo requests, and signups concentrate between Monday and Thursday, with a significant drop-off on Friday afternoons and weekends. This helps sales and marketing teams know when to focus their outreach efforts.
• Restaurants & Retail: A restaurant manager might use weekday analysis to predict foot traffic for staffing shifts. Friday and Saturday will likely be the busiest, but seeing a consistent spike on Tuesdays might prompt a "Tuesday Special" to boost sales even more.
• Content Creators: A YouTuber or blogger could analyze engagement data to discover that posting new content on Wednesday mornings generates the most views and shares.

By transforming transaction dates, signup dates, or event timestamps into simple weekday labels, you can create reports that inform smarter, more timely business decisions.

Creating a Weekday Name with the FORMAT Function

The most direct way to get a weekday name in Power BI is by using the FORMAT function within a calculated column. A calculated column exists at the row level, meaning it runs a formula for each row in your table and stores the result as a new piece of data. This is perfect for static attributes like the day of the week, as it only needs to be calculated once when you refresh your data.

The FORMAT function converts a value into text in a specified format. To get a weekday name from a date, its syntax is simple.

Step-by-Step Guide to Adding a Weekday Name Column

Let's assume you have a table named 'Sales' with a column called 'OrderDate'.

1. Open the Data View: In Power BI Desktop, click on the Data View icon (the little grid) on the left-hand navigation pane.
2. Select Your Table: Choose the 'Sales' table from the Fields pane on the right.
3. Create a New Column: Go to the "Table tools" or "Column tools" tab in the top ribbon and click New column.
4. Enter the DAX Formula: In the formula bar that appears, type the following DAX formula. Replace 'Sales'[OrderDate] with the name of your table and date column.

To get the full weekday name (e.g., "Monday", "Tuesday"):

Weekday Name = FORMAT('Sales'[OrderDate], "dddd")

To get the abbreviated weekday name (e.g., "Mon", "Tue"):

Weekday Abbr = FORMAT('Sales'[OrderDate], "ddd")

Press Enter. Power BI will instantly calculate the weekday name for every single row in your 'OrderDate' column, creating a new 'Weekday Name' column in your table.

The "dddd" format code specifically tells Power BI you want the full name of the day corresponding to that date, while "ddd" gives you the three-letter abbreviation.

Getting the Right Sort Order with a Weekday Number

You've successfully created your "Weekday Name" column and now you want to build a visual, like a bar chart showing sales for each day of the week. But when you drag your new column to the chart's axis, you'll immediately see a problem: the days are sorted alphabetically - Friday, Monday, Saturday, Sunday...

This is obviously not helpful. To fix this, you need to create a second helper column that contains the numerical representation of the day (e.g., Monday=1, Tuesday=2). Then, you'll tell Power BI to use this numerical column to sort the weekday name column.

For this, we'll use the WEEKDAY function.

Using the WEEKDAY Function

The WEEKDAY function returns a number from 1 to 7 that represents the day of the week for a given date. Its syntax is:

WEEKDAY(<date>, [return_type])

The <date> is your date value, and [return_type] is an optional argument that determines which day the week starts on.

The options for return_type are:

• 1 (or omitted): The week starts on Sunday (1) and ends on Saturday (7). This is the default and common in North America.
• 2: The week starts on Monday (1) and ends on Sunday (7). This is the ISO 8601 standard and is common in business reporting globally.
• 3: The week starts on Monday (0) and ends on Sunday (6).

For most business dashboards, using return_type 2 (Monday=1) is the most logical choice. Let's create another calculated column.

Step-by-Step Guide to Adding a Weekday Number Column

1. In the same table, click New column again.
2. Enter the following DAX formula in the formula bar:

Day Number of Week = WEEKDAY('Sales'[OrderDate], 2)

Now you have two new columns: 'Weekday Name' (containing text like "Monday") and 'Day Number of Week' (containing numbers like 1).

Tying It All Together: The Sort By Column Feature

You have the name, and you have the number. The final step is to connect them to fix your visual's sort order.

1. Select the Name Column: While still in the Data View, click to select your 'Weekday Name' column. You should see a border appear around it. When a column is selected, the "Column tools" ribbon at the top becomes active.
2. Choose 'Sort by other column': In the "Column tools" ribbon, find and click the dropdown button labeled Sort by column.
3. Select the Number Column: From the dropdown list, choose your 'Day Number of Week' column.

That's it! You won't see any immediate changes in the data table itself, but this instruction is now saved within your data model. When you return to the Report View, your visuals will automatically update. Any chart, table, or slicer that uses the 'Weekday Name' column will now display the days in their correct chronological order: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.

This step is foundational for creating professional and easy-to-read reports. A properly sorted axis lets users quickly see trends and compare weekend performance versus weekday performance at a glance.

An Alternative: The SWITCH Function

While the FORMAT + WEEKDAY + Sort By Column method is the most efficient, sometimes you might want to create both the weekday number and name within a single, more custom logic, or if your date format is unusual. The SWITCH function offers another flexible way to get weekday names.

You can combine SWITCH with the WEEKDAY function to accomplish this:

Weekday Name Alt = 
SWITCH(
    WEEKDAY('Sales'[OrderDate], 2), -- Using Monday=1 standard
    1, "Monday",
    2, "Tuesday",
    3, "Wednesday",
    4, "Thursday",
    5, "Friday",
    6, "Saturday",
    7, "Sunday",
    "Unknown Day" -- Fallback value
)

Be aware that if you use this method, you will still need to create a separate numerical WEEKDAY column to use for sorting. Because of this extra step, most Power BI developers prefer using the FORMAT function for its directness and simplicity, but SWITCH is a powerful tool to keep in your toolkit for more complex conditional logic.

Final Thoughts

In short, creating weekday analysis in Power BI is a two-part process: use the FORMAT function to get the text-based day name and the WEEKDAY function to get a numerical sorter. The final critical touch is connecting them with the "Sort by column" feature, which transforms messy, alphabetical sorting into clean, chronological order for professional reporting.

Building dashboards, even for seemingly simple tasks like this, often involves wrestling with DAX, data modeling, and finicky formatting in tools like Power BI. With Graphed, we’ve removed that entire learning curve. You can connect sources like Google Analytics or your CRM and simply ask, "show me website sessions by day of the week for this quarter," and get an interactive, live-updating chart in seconds - already sorted correctly, with no formulas required.