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:
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
Hi,
This did not add week to the date hierarchy it is a separate field. I need a chart with Month, week, day options. Do you have any insight to that?
Thanks,
Jac
Hi Jac…To add “Week” to the date hierarchy in Power BI and ensure it’s integrated with Month and Day options, follow these steps:
1. **Create a New Week Column**:
– Go to the “Modeling” tab in Power BI.
– Select “New Column” and use the following DAX expression to create a column for the week number:
“`DAX
Week = WEEKNUM([YourDateField], 1) // 1 for week starting on Sunday, or 2 for Monday
“`
2. **Add the Week Column to the Date Hierarchy**:
Power BI does not automatically add “Week” into the default date hierarchy. Instead, you can create a custom hierarchy by manually combining the fields:
– In the “Fields” pane, locate the date field and the new week column you created.
– Right-click on the date field and select “New Hierarchy.” This will create a hierarchy starting with “Year” and “Quarter.”
– Drag “Month,” “Week,” and “Day” into the hierarchy.
3. **Use the Custom Hierarchy in Your Visualizations**:
– Once the hierarchy is created, you can use it in visuals like line charts, tables, or matrix visuals.
– You’ll be able to drill down from Month to Week to Day in your charts.
This method allows you to have Month, Week, and Day as options in your Power BI chart hierarchy. Let me know if you need further assistance!