How to Extract Year from Date in Power BI DAX

Extracting the year from a date is one of the most common tasks you'll perform in Power BI. Whether you're trying to build a sales report, analyze website traffic trends over time, or simply organize your data, getting just the year value is a fundamental first step. This guide will walk you through the most effective ways to pull the year from a date column using DAX, with clear examples for each method.

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

Why You Need to Isolate the Year

Before jumping into the formulas, it’s helpful to understand why this is so important. Raw date columns (like 10/23/2023) are great for chronologies, but they aren't very useful for aggregation. You can’t easily group all of your 2023 sales together if the date field treats every single day as a unique value. By creating a separate ‘Year’ column, you can:

• Summarize data: Easily calculate total revenue, user sign-ups, or support tickets for an entire year.
• Create hierarchies: Use the Year column in slicers, filters, and chart axes to allow users to drill down from Year to Quarter to Month.
• Perform comparisons: Analyze year-over-year (YoY) growth by isolating metrics for specific years.

Method 1: The Simple and Direct YEAR Function

The easiest and most common way to get the year is by using the YEAR() function. It’s a straightforward function that does exactly what its name suggests: it takes a date as input and returns the four-digit year as a whole number.

Syntax and Usage

The syntax for the function is incredibly simple:

YEAR(<date>)

The <date> argument needs to be a column containing dates in a valid date/time format.

Step-by-Step Example

Let’s say you have a table named 'Sales' with a column called 'OrderDate'. Here’s how you would create a new column containing just the year from the 'OrderDate'.

1. Go to the Data View: In Power BI Desktop, click on the table icon on the left-hand navigation bar to switch to the Data view.
2. Select Your Table: From the 'Fields' pane on the right, select the table that contains your date column (in our case, the 'Sales' table).
3. Create a New Column: In the top ribbon, go to the 'Column tools' tab and click on "New column".
4. Enter the DAX Formula: An editor bar will appear where you can type your DAX formula. Enter the following:

Order Year = YEAR('Sales'[OrderDate])

This formula tells Power BI to create a new column named "Order Year". For each row in the 'Sales' table, it will look at the 'OrderDate' and extract the year value.

1. Press Enter: Hit Enter to confirm the formula. You will immediately see your new "Order Year" column populated with the corresponding years from the 'OrderDate' column.

That's it! The output is a whole number (e.g., 2023, 2024), which is perfect for sorting, filtering, and performing mathematical comparisons. For most use cases, the YEAR() function is the best choice.

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

Method 2: Using the FORMAT Function for Text Output

Sometimes you might need the year as a text value instead of a number. This is useful for concatenating it with other strings in labels or creating custom titles. For this, the FORMAT() function is perfect.

The FORMAT() function is a powerful tool that converts a value into a text string in a specific format.

Syntax and Usage

The syntax for this use case is:

FORMAT(<value>, <format_string>)

To extract the year, you use a date column as <value> and specific year-format codes as <format_string>.

• "yyyy" returns the four-digit year (e.g., "2023").
• "yy" returns the two-digit year (e.g., "23").

Step-by-Step Example

Following the same steps as above to create a new column, you would use this DAX formula:

Order Year Text = FORMAT('Sales'[OrderDate], "yyyy")

You will get a new column that looks identical to the one created by the YEAR() function. However, there's a critical difference: the data type. While YEAR() returns a whole number, FORMAT() returns a text string.

When to Use FORMAT vs. YEAR

You might wonder why you’d choose one over the other. Here's a quick breakdown:

• Use YEAR() when:
• Use FORMAT() when:

Heads Up: If you use FORMAT(), your visuals might sort the years alphabetically (e.g., 2021, 2022, 2023) because they are text fields. This is usually fine, but if you run into sorting issues, it's a good practice to have a numeric year column (from YEAR()) available that you can use for the sorting order ("Sort by" a different column feature in Power BI).

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

Using the YEAR Function Inside a Measure

Calculated columns, like we've created so far, add data to your model row-by-row and increase the file size. For more dynamic analysis, it’s often better to work with measures. You can absolutely use the YEAR() function inside a measure to perform calculations on the fly without storing any extra data.

For example, you could create a measure to calculate the total sales for the most recent year in your dataset.

Example: Calculating Sales for the Most Recent Year

First, let's create a measure to identify the latest year available in your sales data:

Latest Year = MAX('Sales'[OrderDate].[Year])

Or more simply with our calculated column:

Latest Year = MAX('Sales'[Order Year])

Now, you can use that in another measure to calculate sales only for records matching that year:

Sales for Latest Year = CALCULATE( SUM('Sales'[Revenue]), FILTER( 'Sales', 'Sales'[Order Year] = [Latest Year] ) )

This is a much more flexible approach for reporting. The [Sales for Latest Year] measure will automatically update as new data is loaded into your model, without you having to change any formulas.

Power Query vs. DAX: Which Should You Use?

It's worth mentioning that you don't have to use DAX at all to create a year column. You can also do it during the data transformation stage in the Power Query Editor. This is often an even better practice for creating simple columns like 'Year'.

How to Do it in Power Query

1. Click "Transform data" in the Home ribbon to open the Power Query Editor.
2. Select the query (table) that contains your date column.
3. Select the date column itself (e.g., 'OrderDate').
4. Go to the "Add Column" tab in the ribbon.
5. Click the "Date" dropdown, then hover over "Year," and click "Year".

Power Query automatically adds a new column named "Year" with the extracted values. This method is incredibly fast and intuitive.

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

The Verdict: Power Query For Transformation, DAX For Calculation

• Use Power Query when: You are doing initial data shaping and preparation. Creating columns for Year, Month, and Quarter is a classic transformation step. It makes the base table cleaner and the column is created only once during the data refresh, which can be more efficient.
• Use DAX (Calculated Columns) when: The calculation needs to reference other tables or depend on DAX-specific logic that isn't available in Power Query.
• Use DAX (Measures) when: You need an aggregated value that reacts dynamically to user filters, slicers, and interactions in the report. This is the heart of Power BI analysis.

For a simple year column, the Power Query approach is arguably the best practice. However, knowing how to do it in DAX is essential for creating powerful measures.

Troubleshooting Common Errors

Sometimes you might run into issues. Here are a couple of the most common problems and how to solve them.

Error: "Cannot convert value of type Text to type Date."

This happens when you try to use the YEAR() function on a column that Power BI doesn't recognize as a date. Maybe it was imported as text (e.g., "2023-10-23").

• The Fix: The best place to fix this is in the Power Query Editor. Select the column, go to the "Transform" tab, and under "Data Type," choose "Date". This will convert the entire column correctly, resolving the error.

Blank Values Appear

If you have blank rows in your date column, the YEAR() function will return a blank for those rows. This is expected behavior. If you need to handle them differently, you could use an IF condition in your DAX formula, though cleaning up empty data rows is typically the better approach.

Final Thoughts

As you can see, extracting a year is a simple but vital part of building effective Power BI reports. The YEAR() function is your go-to for creating a numeric column for slicing and dicing, while FORMAT() offers flexibility when you need the year as text. For best practice, consider adding your Year column in Power Query as part of your data prep routine.

Building dashboards column-by-column and measure-by-measure in tools like Power BI is powerful but time-consuming. We built Graphed because we wanted to turn hours of data prep and DAX writing into 30-second conversations. After connecting your sources, you can just ask natural language questions like "Show me a trend of our sales by year for the last 5 years" and instantly get the visualization you need, all in real-time and without touching a formula editor.