How do I add a week to the date hierarchy in Power BI?

By /

Adding a week to the date hierarchy in Power BI allows users to group data by a weekly time frame, providing a more granular view of their data. This can be done by selecting the date column in the data model and changing the grouping option to “Week” in the “Format” tab. This will automatically add a new level to the date hierarchy, allowing for more in-depth analysis and reporting. By adding a week to the date hierarchy, users can gain valuable insights into their data trends and patterns over a weekly period, enhancing their overall understanding and decision-making capabilities.

Power BI: 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:

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:

x

Related terms:

  1. How can I add a week to a date hierarchy in Power BI?
  2. How Can the Date Hierarchy Be Removed in Power BI?
  3. How can I use Power BI to get the day of the week from a given date?
  4. How to get day of week from date in SAS?
  5. I want to find the date from a Google Sheets week number. What should I do?
  6. Excel: Get the Date of a Given Week Number
  7. Q: How do I convert a date to a week number in VBA?
  8. How can I convert a date to a day of the week in Microsoft Excel?
  9. How can PySpark be used to round a date to the first day of the week?
  10. What are some examples of getting the first day of week in Power BI?