How to group data by year and month in Power BI?

by

As a data analyst, you’re ofte tasked with analyzing performance for Time-based analysis. In a Sales Analysis use case, you have probably a table with daily sales figures, but you might need to group this data by year and month for a more meaningful trend analysis.

In this tutorial we will assume a very simple data model consisting of a single table: SalesData (Date: date, EmployeeID: text, SalesAmount: currency)

Solution using Power Query (M)

  1. Open Power Query Editor by clicking “Edit Queries” in Power BI Desktop.
  2. Select your SalesData table.
  3. Add a custom column by clicking “Add Column” and then pick “Custom Column”.
  4. Name the column “YearMonth” and use this formula:
= Date.ToText([Date], "yyyy-MM")
  1. Click “OK” to create the column.
  1. Right-click on the new YearMonth column and select “Group By”.
  2. In the Group By dialog:
  • Set “Sales by Month” as the New Column
  • Choose “Sum” as the operation
  • Select “SalesAmount” as the column to sum
  1. Click “OK” to apply the grouping.
  2. Hit Close and Apply to return to Power BI desktop.

Solution using DAX

Use this option if you would like to group your data in your visualization / dashboard.

  1. Create a calculated column in your SalesData table:
YearMonth = FORMAT(SalesData[Date], "yyyy-MM")
  1. Create an explicit measure for total sales:
Total Sales = SUM(SalesData[SalesAmount])
  1. Create a visual (e.g., a line chart) using YearMonth on the axis and Total Sales as the value.

Implementing in a simple Power BI Desktop report

  1. Load your SalesData into Power BI Desktop.
  2. Apply either the Power Query or DAX solution.
  3. Create a line chart:
  • Drag YearMonth to the Axis field
  • Drag Total Sales to the Values field
  1. Format your visual as needed (e.g., change colors, add titles).

This approach allows you to analyze employee performance trends over time, identifying seasonal patterns or year-over-year growth in sales.

Troubleshooting

  • If your dates are not grouping correctly, ensure your Date column is recognized as a Date type in Power Query.
  • For international date formats, you may need to use Date.ToText([Date], "yyyy-MM", "en-US") in Power Query to ensure consistent formatting.
  • If you see blanks in your YearMonth column, check for null values in your Date column and handle them appropriately.
  • For large datasets, consider using Power Query grouping for better performance.