How to Add Week Number in Power BI

Adding week numbers to your Power BI reports lets you analyze trends and performance on a weekly basis, but cracking the DAX formulas to do it right can be a headache. Whether you need your weeks to start on a Sunday, a Monday, or follow international standards, there’s a straightforward way to get it done. This guide will walk you through creating a calculated column for week numbers, including how to handle year-over-year sorting so your visuals make sense.

Why Bother with Week Numbers in Your Reports?

While daily or monthly views are common, weekly tracking is a sweet spot for many businesses. It smooths out the volatile up-and-down noise of daily data while being more responsive than a monthly summary. For marketing and sales teams, week numbers are essential for monitoring performance without having to wait until the end of the month to spot a trend.

Here are a few common use cases:

• Marketing Campaign Analysis: Compare week-over-week campaign performance, like website traffic, lead generation, or ad spend ROI.
• Sales Performance Tracking: Monitor your sales team’s weekly progress against quotas or track the number of deals closed each week.
• E-commerce Reporting: Analyze weekly sales totals, average order value, or conversion rates to quickly react to changes in customer behavior.
• Operations Management: Track key operational metrics like production output, support tickets created, or inventory levels on a weekly cadence.

Adding a reliable week number is the foundation for all of this weekly analysis.

Understanding Power BI's WEEKNUM Function

At the heart of creating week numbers in Power BI is a DAX (Data Analysis Expressions) function called WEEKNUM. While it looks simple on the surface, its real power lies in its optional second argument, which determines when the week officially starts.

The basic syntax looks like this:

WEEKNUM(<date>, [return_type])

• <date>: This is a Date column from your data model - ideally, from a dedicated Date Table.
• [return_type]: This optional number tells Power BI which day of the week to consider as the first. If you leave it blank, it defaults to 1.

Key return_type Options

Here’s a breakdown of the most common return_type values you’ll use:

• 1 (or blank): Starts the week on a Sunday. In this system, Week 1 of the year begins on January 1st, and the next week starts on the following Sunday.
• 2: Starts the week on a Monday. Like the first option, Week 1 begins on January 1st, and the new week begins on the next Monday. This is common in business reporting.
• 21: Follows the ISO 8601 standard, where the week always starts on Monday. This is the international standard for date and time and is crucial for global businesses that need consistency. Under this system, the first week of a year is the one that contains the year's first Thursday.

The Best Practice: Always Use a Date Table

Before we jump into the formulas, you need a proper Date Table in your Power BI model. A Date Table is a separate table containing a continuous list of dates and related columns like year, month, quarter, and day of the week.

Using one ensures your time intelligence functions work correctly and provides a single, reliable source for all date-related analysis. If you're using dates directly from your sales or marketing data, you risk having gaps (like weekends or holidays with no sales) that can break your calculations. You can create one quickly in Power BI using DAX functions like CALENDAR or CALENDARAUTO.

All the examples below assume you have a Date Table named Date with a primary date column also named [Date].

Method 1: Add a Basic Week Number Column

Let's start by adding a standard week number column where the week starts on a Monday. This is a common requirement in many business reports.

Step-by-Step Instructions

1. Navigate to the Data View In the Power BI Desktop, look at the left-hand navigation pane and click on the icon that looks like a table. This is the Data View, where you can see the raw data in your tables.
2. Select Your Date Table From the Fields pane on the right side of the screen, find and click on your Date Table to make it active.
3. Create a New Column With your Date Table selected, the "Table tools" tab should appear in the ribbon at the top. Click on New column. This will open up the formula bar, where you can enter your DAX expression.
4. Enter the DAX Formula Type the following formula into the formula bar and press Enter. This formula creates a week number where each new week starts on a Monday:

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

If your business week starts on a Sunday, you'd use this formula instead:

Week Number = WEEKNUM('Date'[Date], 1)

You will now see a new column in your Date Table named "Week Number" that contains a number from 1 to 53 for each date in your table. You can use this column in your chart axes, slicers, or a table visual to organize your data weekly.

Method 2: Using the ISO 8601 Standard Week Number

If your company operates internationally or just prefers to follow standardized reporting conventions, the ISO 8601 week numbering system is the way to go. It prevents ambiguity by tying the first week of the year to the first Thursday of the year, ensuring every week belongs entirely to one year.

You have two great DAX options for this.

Option A: Using WEEKNUM with return_type 21

Just like our last method, create a new column in your Date Table, but this time use 21 for the return_type.

ISO Week = WEEKNUM('Date'[Date], 21)

This formula tells Power BI to use the ISO 8601 definition, where weeks start on Monday.

Option B: Using the Simpler ISOWEEKNUM Function

Power BI has a dedicated function specifically for this standard, which makes the formula even cleaner. Create a new column with the following DAX:

ISO Week = ISOWEEKNUM('Date'[Date])

This does exactly the same thing as the previous method but is easier to read and remember. Both are correct, so feel free to use whichever one you prefer.

The Critical Step: Creating a Sortable Year-Week Column

Once you start using your new "Week Number" column in visuals, you'll immediately run into a problem. If your data spans multiple years, a chart will group all "Week 1" data together, regardless of whether it was from 2022, 2023, or 2024. Your line chart will look like a chaotic scribble instead of a chronological progression.

To fix this, you must create a combined "Year-Week" property that allows Power BI to sort everything correctly. We'll create two columns: one for a human-friendly label (like "2024-W01") and another for sorting in the background.

Step 1: Create a Numeric Sorting Column

Go to your Date Table, create a new column, and use this DAX formula:

YearWeek Sort = (YEAR('Date'[Date]) * 100) + WEEKNUM('Date'[Date], 2)

Let's break that down:

• YEAR('Date'[Date]) * 100: This takes the year (e.g., 2024) and turns it into 202400.
• + WEEKNUM('Date'[Date], 2): This then adds the week number (e.g., 1) to that result.

The output for the first week of 2024 would be 202401. The output for week 52 of 2023 would be 202352. Since this is a simple number, Power BI can easily sort it in chronological order from smallest to largest.

Step 2: Create a User-Friendly Label Column

Now, let's make a label that looks clean in reports. Create another new column with this formula:

YearWeek Label = "W" & FORMAT(WEEKNUM('Date'[Date], 2), "00") & " - " & YEAR('Date'[Date])

This DAX creates a text label like "W01 - 2024". The FORMAT function with "00" ensures that single-digit weeks get a leading zero (e.g., "W01" instead of "W1"), which keeps your labels looking tidy and consistent.

Step 3: Sort the Label Column by the Numeric Column

This is where the magic happens. We're going to tell Power BI to sort our text-based YearWeek Label column using the logic from our numeric YearWeek Sort column.

• Select the "YearWeek Label" column by clicking on its header in the Data View.
• In the 'Column tools' ribbon at the top, click on the Sort by column dropdown.
• From the list that appears, choose "YearWeek Sort".

Power BI will process for a moment. Now, whenever you use the "YearWeek Label" column in a visual's axis, it will automatically sort chronologically instead of alphabetically. Your charts are now perfect!

Final Thoughts

Adding week numbers in Power BI boils down to using the WEEKNUM function and knowing which return_type fits your business needs. Remember that creating a separate, sortable year-week column is the real game-changer that transforms this from a confusing calculation into a powerful tool for your reports.

While mastering DAX is a valuable skill, it introduces friction between your data and your insights. For our internal analysis, we’ve found that the fastest path is often just asking a question in plain English. Instead of thinking through formulas and sort columns, we use Graphed to ask things like "show me weekly revenue from Shopify vs. Facebook Ads for the last 6 months," which instantly builds a live, multi-source report and frees us up to find the story behind the data.