How to Add Quarter in Power BI

Analyzing company performance on a quarterly basis is one of the most common tasks in business reporting, but Power BI doesn't automatically know what a "quarter" is. To build reports that compare Q1 sales to Q2 sales or track marketing spend each quarter, you first need to add this logic to your data model. This article will walk you through several easy and practical ways to add quarter calculations to your Power BI reports using both Power Query and DAX.

First, A Quick Word on Date Tables

Before you can perform any time-based calculations, you need a dedicated calendar or date table in your Power BI model. This is non-negotiable for serious reporting. A date table is a simple table containing a continuous list of dates, along with columns for month, year, day of the week, and - once we're done - quarter.

Why is this so important?

• It provides context. It ensures your analysis accounts for every single day within a period, even if you had no sales or activity on that day.
• It powers DAX time-intelligence functions. Functions like DATESYTD (Dates Year-to-Date) or SAMEPERIODLASTYEAR rely on a properly structured date table to work.
• It keeps everything organized. You can add all your custom time columns (like fiscal quarter or week number) to one clean table instead of cluttering up your sales or event tables.

If you don't have a date table, you can create one quickly in Power BI using DAX. From the Data view, select New Table from the ribbon and enter one of these formulas:

Dates = CALENDAR(MIN('YourDataTable'[DateColumn]), MAX('YourDataTable'[DateColumn]))

Or, you can use CALENDARAUTO(), which automatically scans your entire model for the earliest and latest dates:

Dates = CALENDARAUTO()

Once you've created your date table, make sure to mark it as a date table by right-clicking it in the Data view and selecting Mark as date table. Then connect it to the date columns in your other tables (like your sales or marketing data) in the Model view. Now, you're ready to add your quarters.

Method 1: Creating Quarter Columns in Power Query

Using the Power Query Editor (also called the "Transform data" window) is often the most intuitive way for beginners to shape data. It provides a user-friendly graphical interface to add columns without writing complex formulas. This method is great because the steps are recorded and applied every time you refresh your data.

Step 1: Get the Basic Quarter Number

First, let's add a simple column that just shows the quarter number (1, 2, 3, or 4).

1. Click on Transform data in the Home ribbon to open the Power Query Editor.
2. In the Queries pane on the left, select your Date table.
3. Select the column that contains your primary date (e.g., "Date").
4. Navigate to the Add Column tab in the Power Query ribbon.
5. Click the Date button, then hover over Quarter, and select Quarter of Year.

Power Query will instantly add a new column at the end of your table named "Quarter" with numbers from 1 to 4 corresponding to each date. While simple, just having a "1" isn't very descriptive in reports.

Step 2: Create a More User-Friendly Quarter Name

Stakeholders prefer seeing names like "Q1" or "Q1 2024" in charts. Let's build that text-based column right here in Power Query.

1. With your Date table still selected, go back to the Add Column tab.
2. This time, click on Custom Column. A new dialog box will appear.
3. In the "New column name" field, type something descriptive like "Quarter Name".
4. In the "Custom column formula" box, you can concatenate a "Q" with the quarter number you just created. Your formula will look like this, using Power Query's M language:

= "Q" & Number.ToText([Quarter])

This formula finds the [Quarter] column, converts its number to text, and prepends "Q" to it. Click OK, and you'll now have a column with values like "Q1," "Q2," etc. This is better, but it's still missing the year, which is essential for multi-year analysis.

Step 3: Combine the Quarter and Year

The most useful format for quarterly analysis combines the quarter and the year, for example "2024-Q1". This format sorts chronologically in most visuals by default.

Let's make one last custom column with everything we need.

1. Go to Add Column > Custom Column again.
2. Name this new column "Quarter & Year".
3. For this, we first need to extract the year from our date column. We'll use the Date.Year function. Then combine it with our Quarter column. Here's the formula:

= Number.ToText(Date.Year([Date])) & "-Q" & Number.ToText([Quarter])

Let's break that down:

• Date.Year([Date]) extracts the year from your main date column.
• Number.ToText(...) converts the numbers (both year and quarter) into text so they can be joined.
• The & "-Q" & is the glue that combines everything into our desired "YYYY-QN" format.

Once you're done, click Close & Apply in the Home ribbon to load your new columns into the data model.

Method 2: Using DAX to Create Quarter Columns

DAX (Data Analysis Expressions) is Power BI's formula language. Adding columns with DAX is done directly in the main Power BI report or data view, not Power Query. This method is often faster and more flexible, especially if you need to create complex custom logic.

Go to the Data view (the grid icon on the left), and select your Date table.

DAX Formula 1: The Simple Quarter Number

This is the DAX equivalent of the first Power Query step.

1. In the ribbon, click New Column.
2. In the formula bar that appears, type the following DAX formula:

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

Hit Enter, and a new column appears with the quarter numbers 1, 2, 3, or 4.

DAX Formula 2: The Formatted Quarter "yyyy-Qq"

Instead of building the text value piece by piece, like in Power Query, we can use DAX's powerful FORMAT function to get the exact output we want in one go.

1. Create another New Column.
2. Enter this formula:

Quarter & Year = FORMAT('Dates'[Date], "YYYY-\Qq")

The FORMAT function is incredibly useful. The second argument, "YYYY-\Qq", serves as a template:

• YYYY tells DAX to return the four-digit year.
• \Q tells DAX to literally type the letter "Q". The backslash is an escape character to ensure "Q" is treated as plain text.
• q tells DAX to return the quarter number.

This single line of code creates a perfectly formatted column like "2024-Q1" that is ideal for charts and tables.

The Critical Step: Sorting Your New Quarter Column

Here's a common pitfall: if you use a text column like "Q1 2024", "Q2 2024", etc., in a chart, Power BI will try to sort it alphabetically. This leads to an incorrect order, like "Q1 2023, Q1 2024, Q2 2023...".

To fix this, you need to create a helper column with a sortable numeric value and tell Power BI to use it to order your text-based quarter column.

1. Create the Sorting Column: Make a new DAX column in your Date table with this formula:

Quarter Sort = (YEAR('Dates'[Date]) * 100) + QUARTER('Dates'[Date])

This creates a number like 202401 for the first quarter of 2024, 202402 for the second, and so on. This format will always sort chronologically.

1. Apply the Sort:

• In the Data view, click to select your text-based quarter column (e.g., "Quarter & Year").
• In the Column tools ribbon that appears at the top, click the Sort by column button.
• From the dropdown menu, select your new sorting column ("Quarter Sort").

Nothing will visibly change in your table, but now, whenever you use the "Quarter & Year" column in any visual, it will be sorted correctly by time, not alphabetically. This step separates a good report from a confusing one!

Handling Fiscal Quarters

Many businesses don't operate on a calendar year that starts in January. For example, a company's financial year might start on July 1st. In that case, July would be the start of Q1. PowerQuery and DAX can both handle this.

Let's use DAX. A clever way to calculate a fiscal quarter is to use the EDATE function to shift the dates backward or forward before calculating the quarter.

For a company whose fiscal year starts in July, you want a date like July 15th, 2024, to register as Q1, not Q3. You can achieve this by telling DAX to treat every date as if it happened six months earlier.

1. Create a New Column in your Date table.
2. Use this DAX formula:

Fiscal Quarter = "FY" & FORMAT(EDATE('Dates'[Date], -6), "YY") & "-Q" & QUARTER(EDATE('Dates'[Date], -6))

Here's the logic:

• EDATE('Dates'[Date], -6) shifts every date in your [Date] column back by 6 months. A date in July becomes a date in January, and a date in June becomes a date in December of the previous year.
• The rest of the formula then simply calculates the quarter and year from that newly shifted date, giving you the correct fiscal period.

Final Thoughts

Being able to analyze data by quarter is fundamental to tracking business performance. Whether you prefer the visual approach of Power Query or the flexibility of DAX, adding a properly formatted and sortable quarter column to your date table is an essential skill. By doing this, you unlock the ability to quickly build meaningful visuals that tell a clear story about your performance over time.

While mastering formulas in Power BI is a valuable skill, it often highlights a common friction point in data analysis - the hours spent simply preparing the data before you can even begin asking questions. Here at Graphed, we've focused on eliminating that friction. After a one-click connection to your data sources, you can ask for analysis in plain English. Instead of writing DAX, you can simply ask, "show me sales by quarter compared to last year," and Graphed instantly builds the correctly sorted, real-time visual for you.