How to Group by Week in Power BI
While Power BI makes grouping data by year, quarter, or month a simple click, grouping by week requires a little bit of extra work. This is a common hurdle, especially for marketers tracking weekly campaign performance or sales teams monitoring weekly pipelines. This guide will walk you through the most effective and reliable methods for grouping your data by week in Power BI, using clear, step-by-step instructions. We'll cover everything from simple DAX functions to solve sorting issues so you can build accurate and insightful weekly reports.
Why Grouping by Week Is So Useful
Weekly reporting is a standard cadence for many business functions. Marketing needs to see ad spend vs. revenue week-over-week. Sales managers track weekly outreach and deal velocity. Operations teams monitor weekly production or support tickets. Summarizing your data at a weekly level often provides the perfect balance between too much daily noise and not enough monthly detail.
The main challenge is that Power BI’s automatic date hierarchy (Year, Quarter, Month, Day) doesn’t include a "Week" level. This means we need to manually create the logic for it. Thankfully, by adding a couple of simple calculated columns to your date table, you can enable robust weekly analysis across your entire report.
Pro Tip: It’s always best practice to have a dedicated Date Table or Calendar Table in your Power BI model. If you don't have one, these formulas can be applied directly to the date column in your main data table (like an 'Order Date' column), but a separate Calendar table is more efficient and scalable.
Method 1: Using a Calculated Column with the WEEKNUM Function
The simplest way to start is by using the WEEKNUM DAX function. This function returns the week number of a given date, from 1 to 53. Here's how to create a column for it.
Step 1: Go to the Data View
In the Power BI Desktop app, look at the left-hand navigation pane and click on the Data icon (it looks like a small table). Find and select your Calendar table (or the table containing your primary date column).
Step 2: Create a New Calculated Column
With your table selected, you'll see a "Table tools" or "Column tools" tab appear in the top ribbon. Click on New column.
Step 3: Write the WEEKNUM Formula
In the formula bar that appears, you’ll write a simple DAX expression. This tells Power BI to look at each date in your date column and figure out its week number.
Let's break that down:
Week Number = is the name you're giving to your new column.
WEEKNUM() is the function you are using.
'Calendar'[Date] tells the function to look at the 'Date' column in your 'Calendar' table. Be sure to replace this with your actual table and column name.
2 is the return_type. This is an important detail. A return_type of 1 means the week starts on Sunday. A return_type of 2 means the week starts on Monday. Most business reporting assumes a Monday start, so 2 is a common choice.
Once you press Enter, a new "Week Number" column will appear in your table, populated with numbers from 1 to 53 for each date.
The Challenge: Your Weeks Aren't Sorting Correctly
Now that you have your week number, you might drop it into a chart and see two problems. First, it will sort textually rather than numerically (e.g., Week 1, Week 10, Week 11, Week 2). Second, and more importantly, it will lump weeks from different years together - Week 1 from 2023 will be grouped with Week 1 from 2024.
We need to create a smarter column for display and sorting purposes.
Fixing the Multi-Year and Sorting Problem
The solution is to create a display column that combines the year and the week number. This makes it unique and ensures it sorts correctly.
Step 1: Create a combined Year-Week column
Let's add another new calculated column. We'll format the week number to always have two digits (e.g., "01" instead of "1") to help with sorting.
After creating this column, you'll see values like "2024 - W01", "2024 - W02", and so on. This looks professional in charts and slicers, and it sorts chronologically by default because the year comes first.
Now you can use this Year Week column on the X-axis of your charts for clean and accurate weekly reporting. You don't need a custom sort column for this specific text format.
A More Robust Method: Create a 'Week Start Date' Column
While a year-week number combo works well, an even better practice is to group data by the start date of each week. This creates an actual date value (like an anchor for the week) which is unambiguous and can be easily formatted in visuals.
Using another calculated column, we can calculate the date of the first day of the week (e.g., Monday) for any given date.
Step 1: Create the 'Week Start Date' Column
Go to your Calendar table, add a new calculated column, and enter the following DAX formula. This formula assumes your weeks start on Monday.
How This Formula Works:
WEEKDAY('Calendar'[Date], 2) returns the day of the week as a number, where Monday = 1 and Sunday = 7.
Let's say the date is Wednesday, January 10, 2024. WEEKDAY will return 3.
The formula becomes: Jan 10 - 3 + 1 = Jan 8. Jan 8, 2024 was indeed the Monday of that week.
If the date was already Monday, Jan 8th, the formula would be: Jan 8 - 1 + 1 = Jan 8. It correctly identifies itself as the start of the week.
Step 2: Using the 'Week Start Date' in Visuals
You can now drag the Week Start Date column into your chart axes. Power BI will initially show it as a full date-time stamp ("1/8/2024 12:00:00 AM"). To clean this up:
Select the Week Start Date column in the Data view or Fields list.
Go to the Column tools tab in the ribbon.
In the "Formatting" section, change the data type to Date.
Choose a short date format from the "Format" dropdown, like
m/d/yyyy.
Now your axis will have clean labels like "1/1/2024", "1/8/2024", "1/15/2024", which is perfect for time-series charts. Because it's a true date field, it automatically sorts chronologically without any extra work.
Final Thoughts
In this tutorial, we covered how to create effective weekly groupings in Power BI by leveraging calculated columns. Whether you use the WEEKNUM function to create a text-based "Year-Week" label or the more flexible method of establishing a "Week Start Date" column, you are now equipped to build reports that provide valuable weekly insights.
While mastering these DAX functions in Power BI is a valuable skill, it highlights the time and expertise required to prepare your data before you can even begin analysis. At Graphed, we created a tool that automates this entire process. Instead of writing formulas, you can simply ask, “Show me sales by week for the last quarter compared to the previous period,” and get a real-time dashboard instantly. We connect directly to your data sources and use natural language so your entire team can get the answers they need without ever having to write a single line of code.