How to Get Weekday from Date in Power BI

Extracting the day of the week from a date is one of the most common tasks you'll perform in Power BI. Whether you want to see if sales spike on weekends or if website traffic dips mid-week, you first need to turn a date column like "10/26/2023" into a readable day like "Thursday." This guide will show you exactly how to do this using both the user interface and a little bit of DAX.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Is Getting the Weekday So Important?

Analyzing performance by day of the week is a fundamental practice in business intelligence. It helps uncover patterns that would otherwise be hidden in your raw data. By isolating weekdays, you can answer critical questions for different departments:

• Sales & Ecommerce: Do customers buy more on Saturdays? Are Tuesdays our slowest sales day? Should we run promotional offers from Friday to Sunday to maximize revenue?
• Marketing: Which days of the week do our social media posts get the most engagement? Should we send email newsletters on Tuesday mornings instead of Thursday afternoons? Do marketing-qualified leads (MQLs) from paid ads convert better if they come in on a weekday?
• Operations & Support: Is our support team overwhelmed with tickets every Monday morning? Do we need to adjust staffing based on which days see the highest volume of inquiries?

Without converting dates into weekdays, your reports can only show you trends over months, quarters, or years. By adding this one simple transformation, you unlock a deeper, more actionable layer of insight into the weekly rhythms of your business.

Method 1: The Quick and Easy Way with Power Query

If you're just getting started or prefer a no-code solution, Power Query Editor is your best friend. It provides a simple point-and-click interface to add a weekday column to your data model before you even start building visuals.

Follow these steps:

1. Open the Power Query Editor

In Power BI Desktop, find the Home tab in the main ribbon. Click on Transform data. This will open the Power Query Editor, which is where you prepare and clean your data before loading it into your report.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Select Your Date Column

In the Power Query Editor window, find your source data table in the Queries pane on the left. Click on it to see a preview of your data. Now, click on the header of the date column you want to use. The entire column should become highlighted.

3. Add a New "Day Name" Column

With your date column selected, navigate to the Add Column tab in the ribbon at the top of the window. In the "From Date & Time" section, click the Date dropdown menu. A list of options will appear. Hover over Day and then click Name of Day.

Instantly, Power BI will add a new column to your table labeled "Day Name" filled with the corresponding weekday for each date (e.g., "Monday," "Tuesday," etc.).

4. Other Useful Date Options

While you're in that same "Day" submenu, you'll see other useful options you can add with a single click:

• Day: Extracts the day of the month as a number (e.g., 1 through 31).
• Day of Week: In Power Query, this returns the day of the week as a number, where Sunday is 0, Monday is 1, Tuesday is 2, and so on, up to Saturday being 6. This is fantastic for sorting your day names correctly in visuals - more on that later.
• Day of Year: Gives you the day number within the year (1 through 365 or 366).

Once you are done, click Close & Apply in the Home tab to load your newly transformed data into your Power BI report.

Method 2: Creating a Weekday Column with DAX

Sometimes you need to add a weekday column after your data is already loaded into the Power BI report model. Or, you might need more dynamic control than Power Query offers. This is where DAX (Data Analysis Expressions), Power BI's formula language, comes in handy.

We'll use a calculated column and the FORMAT function. A calculated column is a new column you add to your table using a DAX formula, and it's calculated row by row.

Follow these steps:

1. Go to the Data View

In Power BI Desktop, click on the Data icon in the left-hand navigation pane (it looks like a small table). Select the table you want to modify from the Fields pane on the right.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Create a New Column

Click on the Table tools tab in the top ribbon and then click the New column button.

3. Use the FORMAT Function

A formula bar will appear above your data table. Here, you will enter the DAX formula. The FORMAT function is incredibly versatile, it converts a value to text in a specified format. To get the week's day name, we use specific format codes.

To get the full day name (e.g., "Monday"), use this formula:

Weekday Name = FORMAT('YourTableName'[YourDateColumn], "dddd")

To get the abbreviated day name (e.g., "Mon"), use this formula:

Weekday Abbreviation = FORMAT('YourTableName'[YourDateColumn], "ddd")

Make sure to replace 'YourTableName' with the name of your table and [YourDateColumn] with the name of your date column. Press Enter, and Power BI will immediately calculate and populate the new column for you.

When should you use Power Query versus DAX?

• Use Power Query when the transformation is a one-time, structural change to your data before analysis. It keeps things cleaner in some workflows, as the transformation is baked into the import process.
• Use DAX for Calculated Columns when you're already in the report-building stage, want to try various approaches quickly, need calculations that respond to report filters, or need more advanced formatting functionality.

Bonus Tip: How to Sort Weekdays Correctly in Your Charts

You've successfully added a "Weekday Name" column to your report. You build a beautiful bar chart showing sales by weekday and notice a glaring problem: the days are sorted alphabetically. Friday comes before Monday, and your chart is impossible to read in an intuitive way!

This is one of the most common Power BI beginner frustrations, but the fix is simple! You just need to tell Power BI to sort your day name column using a corresponding day number column.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Create a Weekday Number Column

First, you need a column that represents the numeric order of a week (e.g., Sunday = 1, Monday = 2, etc., or whatever your preferred order is). We can do this easily with the WEEKDAY DAX function.

The Syntax is WEEKDAY(<date>, [return_type]). The return_type option allows you to set the rules. There are three widely-used options:

• 1 (or left blank): This counts Sunday as 1 and Saturday as 7. This is the default.
• 2: This counts Monday as 1 and Sunday as 7.
• 3: This counts Monday as 0 and Sunday as 6.

In the Table Tools ribbon, click New Column, and enter this formula:

WeekdaySortOrder = WEEKDAY(YourTable[YourDateColumn], 2)

This creates a column where Monday is 1, Tuesday is 2, and so on, which is perfect for sorting purposes.

Step 2: Apply the Custom Sort

Follow these steps:

• In the Data View, select the Weekday Name column that you want to sort correctly.
• In the Column Tools tab in the ribbon, click on the Sort by Column button.
• From the dropdown list that pops up, choose your WeekdaySortOrder column, the one you just created.

That's it! You won't see any change in the data table, but when you go back to Report view, any visual that uses the Weekday Name column will automatically sort the days in the correct chronological order (Monday, Tuesday, Wednesday, etc.).

Final Thoughts

Learning how to extract and manipulate days of the week is a fundamental Power BI skill that opens the door to deeper, more actionable reports. Whether you use a simple click in Power Query or a flexible DAX formula, you now have the tools to unlock insights about your business's weekly rhythms.

While these techniques are great when building reports manually, they represent the kind of time-consuming data wrangling that is ultimately eliminated. Graphed aims to streamline such tasks by automating report completion, so you can focus on what matters. With Graphed, easily add and sort weekday columns without having to use any DAX or Power Query settings, providing professional-looking charts effortlessly.