How to Show Weekly Data in Power BI

Viewing your data on a weekly basis gives you a powerful rhythm for tracking performance, much more so than a noisy daily view or a slow-moving monthly report. Yet getting Power BI to properly group and display your information by week isn't as straightforward as it seems. This tutorial will walk you through the most effective methods for analyzing weekly data in Power BI, from quick built-in features to the more robust, best-practice approach using a calendar table.

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 Does Weekly Reporting Matter?

Analyzing data weekly hits the sweet spot for many business activities. Marketing campaigns often run week-to-week, sales teams review progress in weekly meetings, and operational metrics can show meaningful changes in a seven-day interval. A weekly view helps you:

• Spot trends faster: You can catch an upward or downward trend a lot sooner than if you waited for a monthly report.
• Smooth out daily noise: Individual daily spikes or dips can be misleading. A weekly aggregate provides a more stable picture of performance.
• Align with business operations: Most teams operate on weekly cycles or sprints, so weekly reports fit naturally into their workflow.

Now, let's get into how to do this in Power BI.

Method 1: Using Power BI's Built-in Date Hierarchy

Power BI tries to be helpful out of the box by automatically creating a hierarchy for any date column you use. This is the quickest way to get started, but it has some important limitations.

Step-by-Step Guide:

1. Make sure you have a column in your data model that Power BI recognizes as a date type. You can check this in the 'Data' view, it should have a calendar icon next to it.
2. Drag your date field onto the axis of a visual, like a bar or line chart. For this example, we'll put 'OrderDate' on the X-axis and 'Total Sales' on the Y-axis.
3. You'll notice Power BI automatically groups the date into Year, Quarter, Month, and Day. You can see these in the 'Fields' pane under your visual.

You can use the drill-down icons in the visual's header to move between these levels. However, you'll quickly see the problem: there is no "Week" level. Power BI's automatic hierarchy is great for standard calendar periods, but for weekly analysis, we need a more specialized solution.

Method 2: Creating a DAX Calendar Table (The Best-Practice Approach)

For flexible and powerful time intelligence, the gold standard in Power BI is to create a dedicated calendar table, also called a date table. This is a separate table that contains a continuous list of dates and additional columns for things like week number, month name, and financial quarter. Creating this table with DAX (Data Analysis Expressions) is highly recommended.

So, why is a calendar table so essential?

• It creates a single source of truth for time. All your time-based calculations can refer to this one table, ensuring consistency.
• It handles all dates correctly. It includes all dates within a range, even if your sales or marketing data has gaps (e.g., weekends with no sales), which is critical for accurate calculations like moving averages.
• It’s highly customizable. You can add any column you need: week number, year-week, fiscal periods, etc.

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 the Basic Calendar Table

First, navigate to the 'Data' view on the left-hand pane in Power BI Desktop. Then, in the 'Table tools' ribbon at the top, click on 'New table'.

In the formula bar, enter the following DAX formula. CALENDARAUTO() scans your entire data model for the earliest and latest dates and creates a full calendar between them.

Dates = CALENDARAUTO()

Press Enter. You'll now have a new table named 'Dates' with a single column called 'Date' containing a continuous sequence of days.

Step 2: Add a Week Number and Sorting Columns

With your new 'Dates' table selected, it's time to add columns that will help us group by week. Click the 'New column' button in the ribbon for each of the following formulas.

Add the Year

This is straightforward, it extracts the year from the date.

Year = YEAR('Dates'[Date])

Add the Week Number

We'll use the WEEKNUM function. This function takes two arguments: the date, and an optional second argument that defines the start of the week. Using 2 tells Power BI that the week starts on a Monday.

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

Create a Formatted 'Year-Week' Column for Chart Labels

Alphabetical sorting will cause "2024-10" to come before "2024-2". To fix this, we need a consistent text format. We will create a YYYY-WW format, using FORMAT to add a leading zero to single-digit weeks (e.g., '01', '02').

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

Create a Sort Column

Since the 'Year-Week Label' is a text field, Power BI will sort it alphabetically by default. To ensure it sorts chronologically (e.g., 2023-W52 followed by 2024-W01), we need a helper column to sort it by. A simple numeric key like YYYYWW works perfectly.

Year-Week Sort = ('Dates'[Year] * 100) + 'Dates'[Week Number]

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 3: Set Your Table Relationships

Now, go to the 'Model' view from the far-left pane. Find your new 'Dates' table and your main data table (e.g., 'Sales'). Click and drag the 'Date' column from your 'Dates' table and drop it onto the date column in your 'Sales' table (e.g., 'OrderDate').

This creates a one-to-many relationship, linking your dedicated calendar to your factual data. It's the central connection that makes all the analysis work.

Step 4: Use Your New Columns in a Visual (and Fix Sorting)

Go back to the 'Report' view.

1. Create a chart. For the X-axis, drag in your new 'Year-Week Label' column from the 'Dates' table.
2. You'll notice it sorts alphabetically. To fix this, select the 'Year-Week Label' column in the 'Data' pane on the right.
3. The 'Column tools' tab will appear. Click 'Sort by column' and choose your 'Year-Week Sort' column from the dropdown.

The visual will instantly update, showing your weeks in the correct chronological order. You've now successfully built a report grouped by properly sorted weeks!

Method 3: Grouping Weeks Using Power Query

Alternatively, you can add week number columns directly to your data table during the data import and transformation step using the Power Query Editor. This is useful for simpler models or when you prefer to do your transformations upstream.

When to Use Power Query vs. DAX

• Use Power Query when the transformations should happen once, when the data is refreshed. This "bakes" the values into a column in your table.
• Use DAX for creating dynamic calculations and for building a central, reusable calendar table that can analyze data across multiple tables.

Step-by-Step Power Query Guide

1. On the 'Home' ribbon in Power BI, click 'Transform data' to open the Power Query Editor.
2. Select the data table containing your date column.
3. Select the date column. Then, in the ribbon, select the 'Add Column' tab.
4. Click 'Date', scroll to 'Week', and choose 'Week of year'. A new column named 'Week of Year' will be added.
5. To specify the week start day, you can't use the menu. Instead, create a custom column. Click 'Custom Column' from the 'Add Column' tab. Give it the name "Week Number (Mon Start)" and use this Power Query M formula:

Date.WeekOfYear([YourDateColumnName], Day.Monday)

1. You would then add another column for the year ('Add Column' > 'Date' > 'Year') and merge them into a combined text column just as we did with DAX.
2. Once you're finished, click 'Close & Apply' on the 'Home' tab to load the changes back into your data model.

While this method works, it's generally less flexible than the DAX calendar table, as you'd need to add these columns to every table with a date you want to analyze.

Practical Tips for Weekly Dashboards

Once you have weekly grouping working, you can create more advanced insights.

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

Week-over-Week Change

A great benefit of a proper date table is calculating week-over-week (WoW) performance. You can create a new measure with this DAX pattern:

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

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

You can then display this percentage in a KPI card or a table to show momentum.

4-Week Moving Average

To smooth out single-week volatility and see the underlying trend, a moving average is perfect. Use DAX to create a new measure:

4-Week Moving Avg Sales = 
AVERAGEX(
    DATESINPERIOD(
        'Dates'[Date],
        LASTDATE('Dates'[Date]),
        -28,
        DAY
    ),
    [Total Sales]
)

Adding this measure as a line on your weekly sales bar chart will clearly show the sales trend over time.

Final Thoughts

Analyzing data on a weekly basis bridges the gap between high-level monthly summaries and chaotic daily figures. While you can use Power Query, building a dedicated DAX calendar table is the most scalable and powerful method for enabling robust weekly reporting and all other forms of time intelligence-based analysis in Power BI.

Of course, manually creating calendar tables and writing DAX formulas, while powerful, represents a significant hurdle for many teams. Getting the relationships correct, handling sorting logic, and then writing further measures for week-over-week comparisons can turn into hours of work. Here at Graphed we’ve made this process as simple as asking a question. You just connect your data sources and describe what you need, like, "show me weekly revenue from Shopify versus our Facebook Ads spend," and we build the dashboard for you with live data. It's the power of in-depth analysis without the lengthy setup or technical learning curve.