How to Add Week to Date Hierarchy in Power BI

Trying to analyze performance on a week-by-week basis in Power BI can feel surprisingly complicated. While Power BI’s automatic date features are great, they don't include a built-in weekly option that works for week-to-date (WTD) analysis. This article will show you exactly how to build a custom week-to-date hierarchy, giving you the control you need to create clear and accurate weekly reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Power BI's Default Hierarchy Falls Short

When you add a date field to a Power BI report, it automatically creates a hierarchy of Year, Quarter, Month, and Day. This is super helpful for most standard reports, but it leaves a big gap for anyone who plans or reports in weekly cycles, like e-commerce stores tracking weekly sales, social media managers reporting on weekly campaign performance, or sales teams reviewing weekly targets.

You can’t just add a “Week Number” column and expect it to work, either. A standalone week number like "34" is meaningless without the year. Is that week 34 of 2023 or 2024? For your reports to be useful, you need a hierarchy that properly groups weeks within their parent year. The best way to achieve this is by creating a dedicated date table.

Creating a Custom Date Table: The Foundation of Your Hierarchy

A custom date table, often called a calendar table, is a best practice in Power BI modeling for a reason. It gives you a robust, single source for all things time-related and allows you to add any custom columns you need - like our weekly data. Here’s how to build one from scratch using DAX.

Step 1: Create a New Table

First, navigate to the Report View or Data View in Power BI. Find the Modeling tab in the top ribbon and click on New table. This will open the formula bar, where you'll enter your DAX expression.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Generate Your Dates with CALENDARAUTO()

There are a few ways to generate a list of dates, but the most efficient is CALENDARAUTO(). This function scans all the date columns in your entire data model, finds the absolute earliest and latest dates, and then creates a single-column table containing every day between them. It’s a fantastic way to ensure your date table always covers your full data range.

In the formula bar, type the following DAX function:

Date Table = CALENDARAUTO()

Press Enter, and Power BI will instantly generate a table named "Date Table" with a single column called "[Date]". This list of dates will serve as the foundation for our hierarchy.

It’s important to format this new column as a date type that you prefer. Select the Date column, go to the Column tools tab, and choose your preferred format. A short date format without the time is usually best.

Adding Weekly Columns with DAX

Now that you have your base date table, it's time to add the columns that will define the weekly periods. We'll do this by adding calculated columns using DAX.

In the Modeling tab, with your "Date Table" selected, click on New column for each of the following formulas.

1. Year Column

First, we need a column to identify the year. This is an easy one.

Year = YEAR('Date Table'[Date])

This column will serve as the top level of our hierarchy.

2. Week Number Column

Next, let's get the week number for each date. The WEEKNUM function is perfect for this. We’ll add a second argument, 2, which tells Power BI that our weeks start on Monday - a common business standard.

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

If your weeks start on Sunday, you would use a 1 instead of a 2.

3. Week Start Date Column

A week number alone isn't very descriptive for reporting. A better practice is to use the start date of the week as a label (e.g., "08/19/2024"). This column calculates the date of the Monday for any given date.

Week Start Date = 'Date Table'[Date] - WEEKDAY('Date Table'[Date], 2) + 1

Let's break that down:

• WEEKDAY('Date Table'[Date], 2) returns a number from 1 (Monday) to 7 (Sunday).
• Subtracting that number from Date, then adding 1, lands on the Monday of that week.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

4. Text-Formatted Week Label

While the Week Start Date is great, some users prefer a cleaner label like "Wk 34". The FORMAT function lets us create this easily. This will make your chart axes much easier to read.

Week Label = "Wk " & FORMAT('Date Table'[Week Number], "00")

The "00" ensures that single-digit weeks are displayed with a leading zero (e.g., "Wk 01", "Wk 02"), which is important for proper visual alignment.

5. A Sort Column to Keep Everything in Order

This is arguably the most important column you'll create. If you try to sort your report by the Week Label column, it will sort alphabetically ("Wk 1", "Wk 10", "Wk 11", "Wk 2"...). To fix this, we need a numeric column that Power BI can use to sort correctly. This column will combine the year and week number into a single, sortable number.

YearWeekSort = ('Date Table'[Year] * 100) + 'Date Table'[Week Number]

For Week 34 of 2024, this formula calculates (2024 * 100) + 34, which results in 202434. This number will always sort logically and chronologically.

After creating this column, select the Week Label column in your data view. Go to the Column tools tab, click Sort by column, and choose your new YearWeekSort column. Now, anytime you use Week Label in a chart or table, Power BI will use the YearWeekSort column to put it in the correct order.

Assembling Your Data Model

With all your columns created, there are two final steps to make your new date table the central hub for all date-based reporting.

Step 1: Mark as Date Table

Telling Power BI that this is your official date table unlocks powerful time-intelligence functions like YTD, QTD, and MTD. In the Data view or Report view, right-click on your "Date Table" in the field list on the right and select Mark as date table, then confirm the date column.

Step 2: Create the Relationship

Next, you need to connect your new date table to your main data table (e.g., your Sales table, Marketing Funnel table, or Web Events table). Switch to the Model view. You'll see boxes representing each of your tables. Find the Date column in your "Date Table" and the corresponding date column in your main data table. Drag the Date column from your "Date Table" and drop it onto the date column in your main data table.

A line will appear between them, creating a relationship. This tells Power BI how to filter your data when you use fields from your date table.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Build the Hierarchy

Finally, let’s build the hierarchy. In the Fields pane on the right, right-click your Year column and select Create hierarchy. Power BI will create a new item called "Year hierarchy". You can rename this to "Weekly Hierarchy" for clarity.

Now, simply drag your Week Label column into this new hierarchy, right underneath Year. And just like that, you have a drillable, chronologically-sorted hierarchy ready for your reports.

Using Your New WTD Hierarchy in a Report

The payoff for all that work is creating insightful reports in seconds. Drag a visual onto your report canvas, like a line or column chart. Add your new "Weekly Hierarchy" to the X-axis and a measure like Total Sales to the Y-axis. You can now use the drill-down arrows on the visual to move seamlessly from a yearly view to a weekly one, empowering you to spot trends and analyze WTD performance with ease.

Final Thoughts

Building a custom date table for WTD analysis in Power BI is a perfect example of its power and flexibility. By using a few simple DAX functions, you can move beyond the tool's limitations and create hierarchies that match exactly how your business operates, making your weekly reporting faster, cleaner, and more accurate.

While mastering DAX is a powerful skill, sometimes you just need a quick answer without becoming a BI developer. At Graphed, we’ve created a way to handle this data work for you. By connecting data sources like Google Analytics, Shopify, or your ads platforms, you can use plain English to ask questions like, "Show me last quarter's sales by week as a bar chart" and get an interactive visualization back instantly. We streamline the entire process of data connection, modeling, and reporting so you can focus on insights instead of setup.