How to Convert Week Number to Date in Power BI

Got a dataset with week numbers and need to turn them into actual dates for your Power BI report? You're in the right place. Trying to build a trendline chart or filter by a specific quarter using just a week number can feel clumsy and limiting. This article will walk you through a clear, step-by-step process to convert week numbers into specific start-of-the-week dates using a simple DAX formula in Power BI.

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 Converting Week Numbers to Dates?

While week numbers are useful for high-level summaries, they hit a wall when you need to perform deeper time-based analysis. Real dates are the foundation of effective business intelligence. Here’s why making the conversion is so important:

• Accurate Visualizations: Power BI’s built-in time intelligence features, like creating trend lines on charts, rely on a proper date column. Without it, you're stuck with clunky bar charts that don't effectively show change over time.
• Proper Sorting: If you try sorting by a week number from 1 to 52 across multiple years, your report will group week 1 of 2023 next to week 1 of 2024. This isn’t helpful for seeing a continuous timeline. A date column ensures everything sorts chronologically.
• Flexible Filtering: Converting to a date allows you and your report viewers to filter and slice data by day, month, quarter, or year - something impossible with a standalone week number.
• Meaningful Comparisons: It enables powerful comparisons like "this week last year" or "month-over-month growth," which are critical for measuring performance.

Simply put, converting week numbers unlocks the full analytical power of your data in Power BI.

The Logic Behind the Conversion

Before jumping into the Power BI interface, let's break down the logic. The goal is to calculate the start date of any given week. To do this, we need three pieces of information, which you should already have in your data table:

1. The Year (e.g., 2024)
2. The Week Number (e.g., 15)
3. Your business’s defined start of the week (usually Sunday or Monday)

The calculation process works like this:

1. Find the very first day of the given year (e.g., January 1, 2024).
2. Determine which day of the week that first day was (e.g., January 1, 2024, was a Monday).
3. Calculate the date of the first day of Week 1 for that year.
4. Add the appropriate number of days to get to the start of the desired week number.

This may sound complicated, but a single DAX (Data Analysis Expressions) formula can handle all of this for us smoothly.

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-by-Step Guide to Creating a Date with DAX

Let’s build the DAX formula to perform the conversion. We'll add a calculated column to your existing data table. For this example, let's assume your table is named 'Sales Data' and it contains columns named [Year] and [Week Number].

Step 1: Create a New Calculated Column

First, navigate to the Data view in Power BI Desktop by clicking the table icon on the left-hand sidebar. Select the table containing your week numbers. In the "Table tools" ribbon at the top, click on "New column."

This will open up the formula bar where you will enter your DAX expression.

Step 2: Enter the DAX Formula

Copy and paste the following formula into the formula bar. This formula calculates the start date of the week, assuming your week starts on a Monday.

Week Start Date = 
VAR CurrentYear = 'Sales Data'[Year]
VAR WeekNumber = 'Sales Data'[Week Number]
VAR FirstDayOfYear = DATE(CurrentYear, 1, 1)
VAR FirstDayOfWeek = WEEKDAY(FirstDayOfYear, 2) // 2 means Monday is the start of the week

RETURN
FirstDayOfYear - FirstDayOfWeek + (WeekNumber - 1) * 7 + 1

Press Enter to create the column. Let’s quickly break down what this formula is doing.

• VAR CurrentYear & VAR WeekNumber: We start by storing the values from your 'Year' and 'Week Number' columns in variables. This makes the formula easier to read and maintain.
• VAR FirstDayOfYear: This uses the DAX DATE function to calculate the first day of the year (January 1st) for the year in the current row.
• VAR FirstDayOfWeek: The WEEKDAY function determines which day of the week January 1st falls on. The 2 tells Power BI to treat Monday as day 1 and Sunday as day 7. If your business week starts on Sunday, you would change this to 1.
• RETURN ...: This is the main calculation. It takes the first day of the year, adjusts it backward to find the start of Week 1, and then adds 7 days for each week to arrive at the correct start date for the given 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 The Data Type and Format

Once you press Enter, Power BI will create the new column. By default, it might be formatted as "Date/Time." To clean this up, select the newly created 'Week Start Date' column. In the "Column tools" ribbon, change the Data type to Date and then select your preferred format (e.g., mm/dd/yyyy or March 5, 2024).

And that's it! You now have a functioning date column that you can use to build timelines, create hierarchies, and leverage Power BI's time intelligence features.

Common Adjustments and Pitfalls

While the formula above works for most common scenarios, here are a few things to keep in mind to tailor it perfectly to your needs.

What if My Week Starts on Sunday?

This is an easy fix. In the DAX formula, simply change the WEEKDAY function's second argument from 2 to 1:

VAR FirstDayOfWeek = WEEKDAY(FirstDayOfYear, 1) // 1 means Sunday is the start of the week

The rest of the formula can remain the same. This one small change ensures your dates align correctly with how your organization reports on a weekly basis.

ISO Week Numbers vs. Standard Week Numbers

It's important to know where your week number data comes from. Most systems use a standard where Jan 1st is always in Week 1. However, some systems, especially in Europe and in finance, use the ISO 8601 standard for week numbering. The ISO standard defines Week 1 as the first week of the year containing a Thursday.

If your data source uses ISO week numbers, the simple formula above may result in off-by-one-week errors for some dates. True ISO week date conversions require a more complex DAX formula. For most marketing and sales reporting, the standard method is perfectly sufficient.

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

Check Your Logic for the First and Last Weeks

For most analyses, this formula is robust. However, depending on how your data source calculates weeks, you might find an edge case where the end of December is incorrectly assigned to Week 1 of the following year. Always do a quick spot-check on key dates - like the first and last weeks of the year - to ensure your new date column makes sense and aligns with your raw data.

Final Thoughts

Turning a simple week number into a full-fledged date column using DAX is a fundamental skill that dramatically enhances your Power BI reports. It takes you from basic, high-level summaries to dynamic, insightful dashboards that properly analyze trends, seasonality, and performance over time.

While learning DAX is a powerful way to customize reports, we know that spending hours perfecting formulas is time you could be spending on strategy. We built Graphed to remove this friction. Instead of manually creating calculated columns or wrestling with a dozen different tools, you can connect your data sources (like Google Analytics, Shopify, or ads platforms) and just ask in plain English, "Show me my revenue by week for the last six months as a line chart." We automate the entire process of data cleanup and visualization so you can get immediate, actionable insights and spend your time growing your business, not just reporting on it.