How to group values by month and week in Power BI?

by

Vanessa asks:

Just getting started with Power BI and trying to make a simple analysis of sales using a table and a visual. My sales data granularity is by day. Any ideas how to summarize it into a weekly and monthly view.

Group by month and week in Power BI visual

  1. Ensure that your Date table has a month (or week) column – create one if needed.
  2. Locate (or define) the measure you would like to aggregate.
  3. Move to the Report View.
  4. Create a new report Page.
  5. Add a column chart (or other chart types) to your page.
  6. Assign the Month column to the X-Axis and your selected measure to the Y-Axis.
  7. Define a legend if needed.

Aggregate by week and month in a summary table

  1. Open your Power Bi file and Move to the Data tab.
  2. From your Ribbon’s Calculations section, hit on New Table.
  3. Use the Summarize DAX function to group your data accordingly. Here is an example:
Sales by Month and Week = SUMMARIZE (Orders,Dates[Month], Dates[Week Number], "Sales Amount", SUM(Orders[Amount]))

Summarize sales by month in Power BI Example

Understand the Data Model

In this example i will use a very simple Course Sales Data Model which comprises of three tables: Courses, Orders and Dates.

Here’s a screenshot:

Create a week number column in your table

As can be seen above, the Dates table contains a Month column but no specific Week column.

  • In the Data View, open your Dates table.
  • Create a week column using the following DAX statement:
Week Number = WEEKNUM(Dates[Course Start Date])

Group by Month in a visual

WE would like to analyze Orders Amount by group and week . We will define a simple stack column chart to support our analysis:

  • Go to the Report View and Create a new Page
  • Drag a stacked column chart into the canvas.
  • Place the Month and Week number in the X-Axis field.
  • Put the Orders Amount field in the Y-Axis.
  • Now, place the Course Name field in the legend.
  • Make sure to order the X-Axis chronologically instead or alphabetically, by sorting the Month values by Month name.
  • Voila:

Next Learning

How to extract year from date time field in Power Bi tables?