How to Extract Day from Date in Power BI

Getting the day from a date column in Power BI is a fundamental task that opens up a whole new layer of analysis. Whether you want to see if sales spike on weekends or track user activity on specific days of the month, isolating the day component is the first step. This guide will walk you through the most effective methods to extract the day using both DAX calculated columns and the Power Query Editor.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Bother Extracting the Day From a Date?

At first glance, pulling out just the day might seem trivial, but it's a gateway to powerful insights. Your raw date data (e.g., "2023-10-26") includes the year, month, and day, but your analysis often depends on looking at these components separately. Breaking them out lets you ask and answer more specific business questions.

Consider a few common scenarios:

• Weekly Performance Analysis: Are your marketing campaigns more effective on weekdays versus weekends? Do website sign-ups increase on Tuesdays? By extracting the day name ("Monday", "Tuesday", etc.), you can easily compare performance across the days of the week.
• Monthly Trend Analysis: Do orders tend to cluster at the beginning, middle, or end of the month? Extracting the day number (1-31) helps you spot patterns related to pay cycles or billing periods. For example, a subscription service might see most of its renewals in the first five days of the month.
• Filtering for Specific Reports: You might need to build a report that only shows data for Mondays to track the progress of a weekly campaign launch. A dedicated "Day Name" column makes this filtering simple and intuitive for anyone using the report.
• Creating Reusable Date Dimensions: In more advanced models, creating a dedicated "Date Table" is a best practice. This table always includes columns for Day Name, Day of Week, Day of Month, and Day of Year to make time-based analysis consistent and efficient across your entire report.

Ultimately, extracting day information transforms your date column from a simple timestamp into a flexible tool for deeper analytical storytelling.

The Two Main Methods: DAX vs. Power Query

In Power BI, there are two primary environments where you can perform data transformations like extracting the day from a date: DAX formulas or the Power Query Editor. They get you to the same result but in different ways and at different stages of the process.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

DAX (Data Analysis Expressions)

DAX is the formula language used within the Power BI data model itself. When you use DAX to create a calculated column, you are adding a new column to your table after the data has already been loaded from your sources. The calculation is performed and stored for each row in your table.

• Best For: Calculations that depend on other tables or complex in-model logic. It's also useful when you need to quickly add a column without going back into the data transformation steps.
• How it Works: You write a formula in the formula bar, similar to Excel. The result is calculated row by row and refreshed whenever the dataset updates.

Power Query Editor

The Power Query Editor is the data transformation engine that runs before your data is loaded into the Power BI model. This is where you clean, shape, and prepare your raw data. Any transformation you apply here becomes a permanent step in your data refresh process.

• Best For: "Permanent" data shaping and cleansing. Adding columns like day name or day of the week is a perfect use case for Power Query, as it's a static piece of information that doesn't need to be recalculated dynamically within the model. This often leads to cleaner models and better performance.
• How it Works: You use a graphical user interface (GUI) with buttons and menus to apply transformations. Power Query records each step and writes the underlying "M" code for you.

Our Recommendation: For creating day-related columns, using the Power Query Editor is generally the preferred method. It structures the data correctly at the source, which can result in a more optimized and efficient Power BI model.

Method 1: Using DAX to Extract Day Information

If you prefer to work within the data model or need to create a column on the fly, DAX offers a straightforward way to extract day parts from a date using calculated columns.

Step-by-Step: Creating a Calculated Column

1. Navigate to the Data View in Power BI (the grid icon in the left-hand pane).
2. Select the table that contains your date column from the Fields pane on the right.
3. In the Table tools ribbon that appears at the top, click New column.
4. This will open the formula bar where you can enter your DAX expression.

Extracting the Day Name ("Monday", "Tuesday", etc.)

The most common requirement is getting the full name of the day. The FORMAT function is perfect for this.

In the formula bar, type the following DAX expression, replacing 'Sales'[OrderDate] with your table and column name:

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

• 'Sales'[OrderDate]: This is the date column you are referencing.
• "dddd": This format code tells DAX to return the full, spelled-out name of the day (e.g., "Monday").
• If you only want the abbreviated name (e.g., "Mon"), you can use "ddd" instead.

Once you hit Enter, a new "Day Name" column will appear in your table, populated with the correct day for each date.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Extracting the Day Number of the Week (1-7 for Sorting)

When you create a visual with your new "Day Name" column, Power BI will sort it alphabetically (Friday, Monday, Saturday...). To fix this, you need a numeric column to sort it correctly. For this, we use the WEEKDAY function.

Create another new calculated column and use this formula:

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

• 'Sales'[OrderDate]: Your date column.
• 2: This second argument, called return_type, tells Power BI how to number the week. This is an important choice:

We'll look at how to use this column for sorting a bit later.

Extracting the Day Number of the Month (1-31)

If you need the calendar day (e.g., the 15th of the month), the DAY function is refreshingly simple.

Create a new column with this formula:

Day of Month = DAY('Sales'[OrderDate])

This will return a whole number from 1 to 31 corresponding to the day of the month for each date.

Method 2: Using the Power Query Editor

Using Power Query is often the most efficient and robust method for adding these columns to your model. The user-friendly interface makes it easy even if you've never written a line of M code.

Step-by-Step: Adding Day Columns in Power Query

1. From the Home ribbon in Power BI, click Transform data. This will open the Power Query Editor.
2. In the Queries pane on the left, select the query (table) that contains your date column.
3. Now, select the date column you want to work with. The column header will turn green.
4. Go to the Add Column tab in the ribbon. Notice the Date icon in the "From Date & Time" group. This is where the magic happens.

Extracting Day Name and Day of Week

With an approach that feels much easier than writing formulas, you can get all the day information you need in a few clicks.

1. To get the day name ("Monday"): With the date column selected, click the Date dropdown, then hover over Day, and finally click Name of Day. Power Query will instantly add a new column with the full day name for each date.
2. To get the day of the week number (for sorting): Select the original date column again. Go to Add Column > Date > Day > Day of Week. This adds a numeric column. Important: Power Query numbers the days from 0-6 (Sunday=0, Monday=1, etc.). This is different from the DAX WEEKDAY function, but works perfectly for sorting.

You can double-click the new column headers to rename them to something intuitive, like "Day Name" and "Day of Week Sort". Every action you take is automatically recorded as a step in the "Applied Steps" pane on the right.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Putting It All Together: The Crucial Sorting Step

Now that you have your "Day Name" column and a numeric helper column for sorting, it's time to build a visual and make it work correctly.

1. Close the Power Query Editor and click Apply & Close if you used that method.
2. Go to the Report View in Power BI (the bar chart icon).
3. Add a Bar Chart visual to your canvas.
4. Drag your new Day Name column to the X-axis field and a measure like total sales to the Y-axis.
5. You'll immediately see the problem: the days are sorted alphabetically.
6. Here is the fix: Go back to the Data View. Select the "Day Name" column by clicking its header. Now, go to the Column tools tab in the ribbon, click Sort by column, and select your numeric sort column ("Day of Week Number" or "Day of Week Sort").

A small loading icon may appear. Once it's done, navigate back to your report visual. The bar chart will now be perfectly sorted from Sunday or Monday through the end of the week, giving you an accurate and readable view of your data.

Final Thoughts

Extracting the day from a date is a foundational skill in Power BI that unlocks far deeper business insights. Whether you prefer the formula-driven flexibility of DAX or the structured, click-based approach of the Power Query Editor, adding new columns for the day name, day of the week, and day of the month will make your reports more dynamic and your analytical capabilities more robust.

Manually creating columns and configuring visualizations in Power BI is effective, but it involves several steps for even simple analyses. We built Graphed because we believe getting insights from your data shouldn't require you to be a functions expert. Instead of clicking through menus or writing DAX, you can simply connect your data sources and ask questions like, "Compare my revenue by day of the week last month," and get a complete, properly sorted chart in seconds. It automates away the setup so you can spend your time acting on insights, not finding them.