How Do I Summarize Data Weekly in Power BI?

Got a table full of daily transactions, website visits, or leads and need to roll it all up into a weekly summary? You're in the right place. Aggregating daily data into weekly views is one of the most common reporting requests, but surprisingly, it can be a little tricky if you're new to Power BI. This guide will walk you through the most effective and a few alternative ways to summarize your data by week.

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

We'll cover the best-practice method using a calendar table and DAX, a quicker approach using Power Query, and some valuable tips to make your weekly reports even more insightful.

Why You Need a Calendar Table (Don't Skip This!)

Before we touch a single formula, let's talk about the absolute best practice for any time-based analysis in Power BI: using a dedicated calendar table. While it might seem like extra work, creating one will save you countless headaches down the road. A calendar table is simply a separate table in your data model that contains a continuous list of dates and useful information about those dates (like the year, month name, quarter, and, most importantly for us, the week number).

Here’s why it’s so important:

• Time Intelligence Functions: Power BI's powerful time intelligence functions (like DATESYTD, PREVIOUSMONTH, etc.) are designed to work with a proper calendar table. Without one, they become unreliable or won't work at all.
• Consistent Analysis: It allows you to analyze different data tables (e.g., sales, marketing spend, support tickets) using one consistent source of dates.
• Customization: You have full control to add columns that define your business calendar, such as fiscal week numbers, company holidays, or promotional periods.
• Fills Gaps: If your sales data has no transactions on a Sunday, that date won't exist in your table. A calendar table ensures every day is present, so your weekly charts don't have weird gaps or missing data.

You can create a calendar table in seconds using DAX. Navigate to the Data view in Power BI, click "New Table" from the Home or Modeling ribbon, and paste in the following formula:

Calendar = CALENDARAUTO()

This simple function scans your entire data model for the earliest and latest dates it contains and builds a complete table of dates between those bookends. We'll add our weekly columns to this table in the next step.

Method 1: Creating Weekly Summaries with DAX Calculated Columns

This is the most flexible and powerful method for weekly reporting. By adding specific columns for "Week Number" and "Year" to your new calendar table, you can easily slice and dice any of your data by week.

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

Step 1: Create or Select Your Calendar Table

Assuming you followed the step above, you now have a shiny new "Calendar" table with a single column named "[Date]". If you already have a date dimension table, you can use that instead.

Step 2: Add Calculated Columns for Weekly Grouping

With your Calendar table selected in the Data view, we'll add a few new columns using DAX. Click "New Column" in the ribbon for each of the following formulas.

Add a simple 'Year' column:

This helps us differentiate between week 5 of 2023 and week 5 of 2024.

Year = YEAR('Calendar'[Date])

Add the 'Week Number' column:

This is the core of our weekly summary. The WEEKNUM function does all the heavy lifting.

Week Number = WEEKNUM('Calendar'[Date], 2)

Heads up: The "2" at the end of the formula is important. It tells DAX that your weeks start on a Monday. If your weeks start on a Sunday, you would use "1" instead. For business reporting, Monday is almost always the standard.

Create a User-Friendly 'Year-Week' Label:

While having "Year" and "Week Number" columns is great, putting them together creates a clean label for chart axes. It looks better (2024-W05) and handles sorting more reliably.

Year-Week = 'Calendar'[Year] & "-W" & FORMAT('Calendar'[Week Number], "00")

The FORMAT(..., "00") part is a nice touch. It ensures that single-digit weeks are displayed with a leading zero (e.g., "W05" instead of "W5"), which makes sorting your axis labels a breeze.

Step 3: Create the Relationship

Switch to the Model view in Power BI. Find your new 'Calendar' table and your primary data table (e.g., 'Sales'). Click and drag the Date column from your 'Calendar' table and drop it on top of the date column in your 'Sales' table. A line will appear, linking them. This tells Power BI how the two tables are related, allowing your calendar to filter your sales data.

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

Step 4: Build Your Weekly Visualization

Now for the fun part! Go back to the Report view.

1. Drag a Line Chart or Bar Chart visual onto your canvas.
2. From your 'Sales' table, drag your metric (e.g., 'Revenue' or 'Transaction Count') into the Y-axis field.
3. From your 'Calendar' table, drag your newly created Year-Week column into the X-axis field.

Instantly, you'll see your data summarized by week! Because we created the Year-Week label, it should automatically sort chronologically. If it doesn't, click the three dots on the visual, select "Sort axis," and ensure it's sorted by the Year-Week column and in ascending order.

Method 2: Weekly Grouping Directly in Power Query

Sometimes you don't need the day-level detail at all. If you want to permanently transform your data into a weekly summary before it even loads into the data model, you can use Power Query.

Warning: This method is destructive, meaning it removes the daily data from your dataset. Only use this if you are absolutely sure you won't need to drill down to the daily level in your reports.

Step 1: Open the Power Query Editor

From the Home ribbon in Power BI, click "Transform data." This will open the Power Query Editor where you can reshape and clean your data.

Step 2: Add a "Start of Week" Column

Select your primary data table (e.g., 'Sales') from the list of queries. Find your date column and select it.

• Go to the Add Column tab in the ribbon.
• Click on the Date dropdown menu.
• Hover over Week and select Start of Week.

This will add a new column to your table where every row shows the date of the Monday for that week.

Step 3: Use the Group By Function

Now we'll use this new column to group our rows together.

• Go to the Home or Transform tab and click Group By.
• In the dialog box, make sure it's set to group by your Start of Week column.
• Next, define your aggregations. For example:
• You can add multiple aggregations, such as counting the number of transactions or averaging the order value.

Click "OK," and your entire table will be transformed into a weekly summary. All your daily data is gone, replaced by one row for each week with the aggregated values you defined.

Step 4: Close & Apply

Click "Close & Apply" in the top-left corner. Your Power BI model will now contain a clean weekly summary table, ready for visualization.

Tips for Better Weekly Reporting

Just summarizing by week is often the first step. Here are a few ways to add more context and make your reports even more useful.

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

1. Creating Week-over-Week Metrics

Your team will inevitably ask, "How did this week perform compared to last week?" You can answer this with a simple DAX measure. First, create a base measure for your core metric:

Total Sales = SUM(Sales[Revenue])

Then, create a measure for the sales in the prior week using the DATEADD function:

Sales Last Week = CALCULATE([Total Sales], DATEADD('Calendar'[Date], -7, DAY))

Now you can create a measure for the Week-over-Week percentage change:

WoW % Change = DIVIDE([Total Sales] - [Sales Last Week], [Sales Last Week])

Add this final measure to a card visual or as a value in a table to show your growth trajectory instantly.

2. Be Mindful of Partial Weeks

When you're looking at the current week, remember that its data is incomplete. This can make your line chart look like it's taking a nosedive. You might want to filter out the current week from trend charts or use a specific note or visual treatment to indicate it’s a partial period.

3. Align Marketing and Sales Data

The beauty of the centralized calendar table is that you can now pull in data from other sources, like Google Analytics or your ad platforms. As long as you relate them all to the same calendar table, you can build powerful visuals showing weekly marketing spend vs. weekly sales revenue on the same chart, perfectly aligned.

Final Thoughts

Summarizing data weekly in Power BI becomes straightforward once you adopt the calendar table method. Adding calculated columns in DAX gives you the ultimate flexibility to analyze week-over-week trends, build dynamic charts, and combine different data sources seamlessly. For permanent transformations, grouping in Power Query offers a clean, effective alternative.

While mastering these Power BI techniques is powerful, it often requires time to configure the model, write the right formulas, and build visuals from scratch. We built Graphed to remove this friction entirely. Instead of manual setup, you can connect your data and just ask a question like, "Show me my revenue by week compared to last year's" and instantly get a live, interactive dashboard. We handle all the connections and calculations in the background, freeing you up to focus on strategy instead of report building.