How to Show Year and Quarter in Power BI

Displaying your key metrics by year and quarter is one of the most fundamental requirements of business reporting. While it sounds simple, getting Power BI to show "2024-Q1" in a way that looks good and sorts correctly requires a few specific steps. This article will walk you through exactly how to create and format year and quarter columns in your Power BI reports using both DAX and Power Query.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

The Foundation: Why You Need a Date Table

Before creating any time-based calculations, it’s essential to have a proper Date Table (sometimes called a Calendar Table) in your data model. Trying to perform date calculations using the date columns from your sales, marketing, or operations tables can lead to inaccurate results and clumsy formulas.

A dedicated Date Table provides a single, unbroken list of all dates within a given period. It acts as the central source of truth for time, allowing you to slice and dice any of your data consistently. When you create relationships from your Date Table's date column to the date columns in your other tables (like [OrderDate], [SignupDate], etc.), all your time intelligence functions will work reliably.

If you don’t have one yet, you can create a simple one in moments using DAX.

1. Navigate to the Data view in Power BI.
2. From the Home tab, click New Table.
3. Enter the following DAX formula. This will create a table with a single column of dates from January 1, 2022, to December 31, 2025. You can adjust these dates to fit your data.

Date = CALENDAR(DATE(2022, 1, 1), DATE(2025, 12, 31))

With this foundation in place, you’re ready to add your Year and Quarter columns.

Method 1: Creating Separate Year and Quarter Columns with DAX

The most straightforward way to analyze data by year and quarter is to create separate columns for each. This method is great for building visuals like a matrix table where you can drill down from the year into the quarters.

We’ll add these as calculated columns to our Date table.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Create the Year Column

1. While in the Data view, make sure your Date table is selected.
2. From the Table tools ribbon (or by right-clicking the table name), select New column.
3. In the formula bar, enter this simple DAX function:

Year = YEAR('Date'[Date])

This formula extracts the four-digit year from each date in your [Date] column. Press Enter, and you’ll see a new column populated with the corresponding year.

Step 2: Create the Quarter Column

1. With the Date table still selected, click New column again.
2. This time, we’ll use the QUARTER function to get the quarter number:

Quarter Number = QUARTER('Date'[Date])

This will return a number from 1 to 4 for each date. Now you have a Year column and a Quarter Number column.

You can use these two columns in a chart by dragging Year into the visualization axis, followed by Quarter Number. This automatically creates a hierarchy, letting users see yearly totals and then click to expand and see the quarterly breakdown.

Method 2: Combining Year and Quarter into One Column (YYYY-Q#)

Often, you want a single, clean field that combines the year and quarter for axis labels, such as "2024-Q1". This looks much cleaner in bar charts or line graphs where a hierarchical breakdown isn't necessary.

Let’s create another calculated column in our Date table for this format.

1. In the Date table, create a New column.
2. Enter the following DAX formula in the formula bar:

Year Quarter = FORMAT('Date'[Date], "YYYY") & "-Q" & FORMAT('Date'[Date], "q")

Let’s break down what this formula does:

• FORMAT('Date'[Date], "YYYY"): Extracts the 4-digit year from the date column and formats it as text.
• & "-Q" &: The ampersand (&) is used to concatenate (join) text strings. This part adds the literal text "-Q" after the year.
• FORMAT('Date'[Date], "q"): Extracts the quarter number (1, 2, 3, or 4) from the date. The lowercase "q" ensures it just gives the number.

When you press Enter, you'll have a new text column with values like "2023-Q1", "2023-Q2", etc. But if you try to use it in a visual right away, you’ll notice a big problem: it sorts everything alphabetically.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

The Crucial Step: Solving the Sorting Problem

By default, Power BI sorts text columns alphabetically. This means your "Year Quarter" column will be sorted incorrectly, showing all the Q1s together, then Q2s, and so on (e.g., 2023-Q1, 2024-Q1, 2023-Q2, 2024-Q2). We need to fix this so it sorts chronologically.

The solution is to create a numeric "helper" column that represents the correct chronological order, and then tell Power BI to use that helper column to sort our Year Quarter column.

Step 1: Create a Numeric Sort Column

We need a formula that generates a unique number for each year-quarter combination, where the number for a later period is always larger than the number for an earlier period.

1. Still in your Date table, create yet another New column.
2. Name it Year Quarter Sort and use this formula:

Year Quarter Sort = (YEAR('Date'[Date]) * 10) + QUARTER('Date'[Date])

This simple trick works perfectly. Here’s why:

• For Q1 2024, the result is (2024 * 10) + 1 = 20241.
• For Q2 2024, the result is (2024 * 10) + 2 = 20242.
• For Q1 2025, the result is (2025 * 10) + 1 = 20251.

As you can see, this numerical sequence will always increase as time moves forward, providing the perfect logical order for sorting.

Step 2: Apply the "Sort by Column" Feature

Now, we just need to associate our Year Quarter text column with our new numeric Year Quarter Sort column.

1. In the Data view, select the Year Quarter column (the text one you want to show in your charts).
2. A Column tools tab will appear in the top ribbon. Click on it.
3. Find the Sort by category group and click on the Sort by column icon.
4. A dropdown menu will appear. From this menu, select your newly created helper column: Year Quarter Sort.

Wait a moment for Power BI to apply the change. Now, go back to your report view. Any visual using the Year Quarter column on its axis will automatically be re-sorted chronologically. Your chart will correctly display the data in the order of 2023-Q4, 2024-Q1, 2024-Q2, etc.

Alternative: Using Power Query for Transformation

For those who prefer to shape their data before it gets loaded into Power BI's data model, you can accomplish all of this in the Power Query Editor. This keeps your data model cleaner with fewer DAX calculated columns since the transformations happen at an earlier stage.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step in Power Query

1. From the Home tab in Power BI, click Transform data to open the Power Query Editor.
2. In the queries pane on the left, select your Date table.
3. Select the Date column by clicking its header.
4. Go to the Add Column tab in the ribbon.
5. Click the Date▼ dropdown button, hover over Year, and select Year. A Year column will be created.
6. Select the Date column again. This time, from the Date▼ dropdown, hover over Quarter and select Quarter of Year. This will add a Quarter column.

Now you have the two separate components. To combine them:

• While still in the Add Column tab, click Custom Column.
• Name your new column something like "Year Quarter".
• In the Custom column formula box, enter:

Text.From([Year]) & "-Q" & Text.From([Quarter of Year])

Similarly, you’ll also need to create your numeric sort key here. Repeat the Custom Column step with a new column named "Year Quarter Sort" and use this formula:

([Year] * 10) + [Quarter of Year]

Once you've created all your new columns, change their data types to ensure Year Quarter is Text and Year Quarter Sort is Whole Number. You can do this by clicking the icon in the column header.

Click Close & Apply in the Home tab of the Power Query Editor.

Once the changes are loaded, you still have to perform the final "Sort by Column" step in the Power BI Data view, just as we did in the DAX method above.

Final Thoughts

Displaying data by year and quarter is essential, and with these straightforward steps, you can create clean, well-organized, and correctly sorted visuals in Power BI. Whether you prefer the flexibility of DAX or the upfront data-shaping power of Power Query, the key is to create both a presentational text column and a numeric helper column for sorting.

The time spent setting up calculated columns and fixing sort orders in tools like Power BI is a common hurdle for teams just trying to get quick answers. At Graphed , we remove this friction by letting you just say what you want. Instead of writing DAX formulas, you can simply ask, "show me website traffic by year and quarter," and we instantly build the correctly formatted and sorted chart for you, pulling live data from your connected sources.