Power BI: How to Add Week to Date Hierarchy


By default, when you add a date field to the x-axis of a visualization in Power BI there is no Week included in the date hierarchy:

However, you can easily add a Week option by creating a new column in your table that includes the week number for the dates and then by adding this new column to the x-axis of your visualization.

The following example shows how to do so.

Example: How to Add Week to Date Hierarchy in Power BI

Suppose we have the following table named my_data in Power BI that contains information about sales made on various dates by some company:

Suppose we attempt to insert a column chart into a report to visualize the total sales by week.

If we drag the Date field to the X-axis and the Sales field to the Y-axis, the date hierarchy for the Date field will not include Week by default:

To add week to the date hierarchy, we must return to the Table view.

Then click the Table tools tab and then click the New column icon:

Then type the following formula into the formula bar:

Week Number = WEEKNUM('my_data'[Date]) 

This will create a new column named Week Number that contains the week number of the year for the corresponding date in the Date column:

Next, we can return to the Report view.

Then, we can drag the new Week Number field to the X-axis of the visualization and the Sales field to the Y-axis:

Power BI add week to date hierarchy

This will produce the following column chart that displays the sum of sales by week number:

The x-axis displays the week number of the year and the y-axis displays the sum of sales for each week.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

Power BI: How to Get Day of Week from Date
Power BI: How to Get First Day of Week
Power BI: How to Filter Between Two Dates

Leave a Reply

Your email address will not be published. Required fields are marked *