Extracting the quarter from a date in Power BI is a common task that allows you to analyze your data at a more granular level. By understanding the distribution of data across different quarters, you can identify trends, patterns, and seasonality in your sales, revenue, or other metrics.

This information can be invaluable for making informed business decisions, such as budgeting, forecasting, and resource allocation. In this post, you will learn how you can extract quarter from date in Power BI using both the Power Query interface and the DAX formula.

Extract Quarter from Date in Power BI Using Power Query

Power Query is an essential tool for Power BI data transformation and manipulation. With an intuitive interface and a wide range of options, you can effortlessly perform diverse transformations.

Additionally, Power Query leverages the M language for further flexibility and precision. In this section, you will see how to extract quarters from date using options available in Power Query.

Extract Quarter from Date with the Transform Menu

The Transform menu in Power Query is a comprehensive toolbox for data manipulation and shaping. It houses a vast array of functionalities, ranging from basic data cleaning and filtering to advanced transformations that use R and Python scripts.

To extract quarter from date, follow these steps.

1. Select the Data column.
2. Go to the Transform tab.
3. Select the Date option.
4. Select the Quarter option to access three different quarter options available in the dropdown menu – Quater of Year, Start of Quarter, and End of Quarter.

Start of Quarter – This option converts the date to the first day of the quarter in which the date falls. For example, if the date is January 15th, the Start of Quarter option will return January 1st.

End of Quarter – This option converts the date to the last day of the quarter in which the date falls. For example, if the date is March 31st, the End of Quarter option will return March 31st.

Quarter of Year – This option returns the quarter number (1, 2, 3, or 4) for the given date. For example, if the date is October 25th, the Quarter of Year option will return 4.

The specific use of each option depends on the analysis you are performing. For example, if you are analyzing sales data, you might use the Start of Quarter option to group sales by the quarter in which they occurred. Or, if you are analyzing customer behaviour, you might use the End of Quarter option to identify trends in customer churn at the end of each quarter.

Extract Quarter from Date with the Add Column Menu

The Add Column menu in Power Query is a dedicated space for creating new columns within your data table. It provides a straightforward interface for defining and constructing columns using various methods.

The Add Column menu and the Transform menu share several similar functions, including the ability to perform overlapping transformations. For instance, both menus offer the Date option. The process of extracting the quarter from a date remains the same in both menus.

When menus overlap, the Add Column version will add a new column to the query, whereas for the Transform menu version, the transformation will occur within the currently selected column. This allows for flexibility in choosing the desired location for the transformation.

There are other options you can use to extract quarter from date in the Add Column tab.

Extract Quarter from Date with the Custom Column Menu

The Custom Column function empowers you to craft new columns with precision using M language expressions. With the Custom Column menu, you gain the ability to leverage intricate formulas and expressions, tailoring new columns to perfectly align with your unique requirements.

The Custom Column menu provides flexibility in customizing the display format of extracted quarters. Instead of displaying them as numerical values, you can opt for a textual representation, incorporating the corresponding year based on the provided dates. Here’s how you can do this.

Step 1 – Go to the Add Column tab and click on Custom Column.

if Date.MonthName(Date.StartOfQuarter([FullDateAlternateKey])) = "April" then "Q2"&"-"&Number.ToText(Date.Year([FullDateAlternateKey]))  else "-"

Step 2 – Use the Custom Column window to create the Values for the New Column. Follow these steps to do this.

1. Enter a descriptive name for the new column you intend to create using the New column name field. This name will appear as the column header for the new column that will be created.
2. Paste the Power Query M formula expression into the Custom column formula box. This will generate the values in the new column.

Here’s a breakdown of the syntax:

• if – This keyword starts the conditional statement.
• Date.MonthName(Date.StartOfQuarter([FullDateAlternateKey])) – This expression gets the month name of the start of the quarter for the value in the FullDateAlternateKey column.
• = “April” – This is the condition that is being checked. If the month name is equal to April, the code will execute the statements following the then keyword.
• then “Q2″&”-“&Number.ToText(Date.Year([FullDateAlternateKey])) – This is the code that will be executed if the condition is true. It returns the string Q2 followed by a hyphen and the year of the date as text.
• else “-“ – This is the code that will execute if the condition is false. It returns a hyphen or dash.

In summary, the expression checks if the month name of the start of the quarter for the value in the FullDateAlternateKey column is April. If it is, it returns the string Q2 and the year of the date as text. Otherwise, it returns a dash.

Once the formula is complete, check the bottom left to validate the syntax and click the OK button to apply.

After initiating it, the syntax goes through every row in the table and returns values based on the set conditions. You can easily modify the syntax to return all the quarters by using a nested IF function.

Extract Quarter from Date with the Advanced Editor

The Advanced Editor is an improvement over the Custom Column window. Basically, the Advanced Editor contains every transformation – in code – that occurs in Power Query. But that’s not all, it’s also a code editor that allows you to write any syntax you want to use for transformation.

You will find the Advanced Editor in two places in the menu tabs – the View tab and the Home tab.

Regardless of the menu tab you use, clicking on Advanced Editor will open a separate window displaying the M code generated by the transformations you’ve applied.

Before adding a new syntax to the existing one, here’s a brief overview of the let and in M code syntax to help you understand it better:

• The Let keyword – is used for defining local variables within an expression, making it particularly valuable for constructing complex expressions that are enhanced in readability and comprehension. In a Let expression, each line of code, except the last, must conclude with a comma to differentiate between lines. The absence of a comma marks the end of the Let expression.
• The In keyword: the in keyword establishes the scope of a local variable, restricting its usage to the subsequent expression. By employing this keyword, you guarantee that variables are evaluated within the intended context.

Now that you have a better understanding of the M code syntax, you can proceed to add a new syntax to the existing one.

#"Added Custom" = Table.AddColumn( #"Removed Other Columns", "Quater", each if Date.MonthName(Date.StartOfQuarter([FullDateAlternateKey])) = "April" then "Q2"&"-"&Number.ToText(Date.Year([FullDateAlternateKey]))  else "-")

Looking closely at this syntax, you will see that it incorporates much of the same syntax used in the Custom Column window. However, here, it’s wrapped in the Table.AddColumn function.

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type)

The Table.AddColumn() function is used to add a new column to an existing table. It uses four arguments – table, newColumnName, columnGenerator, and columnType.

In the syntax:

• table = #”Removed Other Columns”
• newColumnName = “Quarter”
• columnGenerator = “each if Date.MonthName(Date.StartOfQuarter([FullDateAlternateKey])) = “April” then “Q2″&”-“&Number.ToText(Date.Year([FullDateAlternateKey]))  else “-“. The each keyword means the function will evaluate every row in the table.

The columnType argument is optional and so doesn’t appear in this syntax. When you click on Done, the syntax will return the same thing as earlier.

The Advanced Editor provides the flexibility to create more complex syntaxes, enabling you to fine-tune every aspect of your query to meet your specific needs.

Extract Quarter from Date in Power BI Using DAX

Data Analysis Expressions (DAX) offers a comprehensive set of functions, operators, and constants that empower you to perform intricate calculations, aggregations, and data transformations directly within your data models.

In this section, you will see how you can use DAX to extract the quarter from a date using calculated columns.

Extract Quarter from Date with Calculated Columns

A calculated column in Power BI is a new column that is created within a table using a DAX formula. Unlike a regular column that stores raw data, a calculated column is dynamically generated based on the values in other columns or expressions.

This provides flexibility in manipulating and transforming data without modifying the original dataset.

To use a calculated column to extract quarter from date in Power BI, go to the Data view, select the table containing the dates and click on New column under the Table tools tab.

Quater = FORMAT(STARTOFQUARTER('Date'[FullDateAlternateKey]), "\QQ yyyy")

When the new column is created, enter this syntax into the formula bar. The expression extracts the quarter number and year into a new column using two DAX functions – FORMAT and STARTOFQUATER.

• Quarter – This part refers to the name of the new column.
• FORMAT(STARTOFQUARTER(‘Date'[FullDateAlternateKey]), “\QQ yyyy”) –  this part applies the FORMAT function to the STARTOFQUARTER() expression and the format string “\QQ yyyy”.
• STARTOFQUARTER(‘Date'[FullDateAlternateKey]) – this part extracts the start of the quarter for the value in the FullDateAlternateKey column of the Date table.
• “\QQ yyyy” – This is the format string that specifies the desired format for the output.

Conclusions

Extracting the quarter from a date in Power BI is a valuable technique for analyzing data trends and patterns over time. By grouping data into quarters, you can identify seasonal fluctuations, track performance across quarters, and make informed decisions based on quarterly insights.

Whether you use Power Query or calculated columns, extracting quarters is a straightforward process that can significantly enhance your data analysis capabilities in Power BI.