Graphed LogoGraphed
Login

How to Extract Year from Date Column in Power BI

Cody Schneider•

Pulling the year from a date column is one of the most common and necessary tasks you'll perform in Power BI. Whether you're building a sales report comparing year-over-year performance or creating a simple filter for your dashboard, getting just the year is a fundamental step. This tutorial will walk you through the most effective ways to extract the year from your date data, using both the Power Query Editor and DAX formulas.

Why Do You Need to Extract the Year?

Before jumping into the "how," let's briefly cover the "why." Isolating the year from a full date (like 01/15/2023) unlocks several critical reporting capabilities. The timestamp, a specific month, or the day of the week can be too granular for high-level analysis. Extracting the year allows you to:

  • Analyze Annual Trends: Track key metrics like revenue, website traffic, or new customers on a year-by-year basis to spot long-term patterns.

  • Create Filters and Slicers: Allow users of your report to easily select one or more years to filter the entire dashboard, focusing on the time frame they care about.

  • Aggregate Data: Summarize your data annually. For example, you can build a table showing total costs and profits for 2021, 2022, and 2023.

  • Group Data in Visuals: Use the year as a category on the axis of a bar chart or line chart to compare performance across different years in a single visualization.

In short, it's about simplifying your data to get a clearer, big-picture view.

Method 1: The Quickest Way Using Power Query Editor

The Power Query Editor is the data transformation engine inside Power BI. It’s the perfect place to clean, shape, and prepare your data before it gets loaded into your report model. For extracting a component like the year, it’s often the most intuitive and direct method, especially for beginners.

This approach creates a completely new column containing only the year, leaving your original date column untouched and intact. This is great for keeping your foundational data clean while adding the new information you need for your report.

Step-by-Step Instructions

  1. Open the Power Query Editor: From the main Power BI Desktop window, go to the Home tab on the ribbon and click on Transform data. This will launch the Power Query Editor in a new window.

  2. Select Your Date Column: In the editor, find the query (table) that contains your date data in the left-hand pane. Click on it, then find and select the specific date column in the main data preview area. When you click the column header, it should become highlighted.

  3. Navigate to the "Add Column" Tab: At the top of the Power Query Editor, click on the Add Column tab in the ribbon. This tab is for creating new columns based on existing data. (It's important to use "Add Column" instead of "Transform," as Transform would replace your original date column with the year value).

  4. Use the "Date" Function: With your date column still selected, look for the Date option in the "From Date & Time" section of the ribbon. Click the small dropdown arrow next to it.

  5. Select "Year": From the dropdown menu, hover over Year, and then click on Year from the submenu that appears.

That’s it! Power Query will instantly add a new column to your table, typically named "Year," containing the four-digit year extracted from your original date column. You can rename this new column if you wish by double-clicking its header.

Once you’re finished, click Close & Apply in the top-left corner of the Power Query Editor to save your changes and load the new column into your Power BI model.

Method 2: Using DAX for Calculated Columns

Another powerful way to get the year is by using DAX (Data Analysis Expressions). Unlike Power Query, which runs during the data refresh process, DAX creates a "calculated column" that exists within your data model and is calculated row by row. This is done directly in the Power BI Desktop report or data view rather than in the Power Query Editor.

Using DAX can be just as efficient and is preferred when you can't or don't want to modify the source data via Power Query, or when you need the calculation to be more dynamic and reference other parts of your model.

Option A: The YEAR() Function

The simplest DAX function for this job is YEAR(). It takes a date as its input and returns a whole number representing the year. This is the best DAX method because it correctly formats the year as a number, allowing for proper sorting and mathematical comparisons.

Step-by-Step Instructions

  1. Go to Data or Report View: In Power BI Desktop, click either the Report icon (the bar chart) or the Data icon (the table) in the left-hand navigation pane.

  2. Select the Right Table: In the Data pane on the right side of the screen, make sure you have the correct table expanded so you can see its columns.

  3. Create a New Column: With the table selected, click on the New column button in the Table tools tab in the top ribbon.

  4. Write the DAX Formula: An FX formula bar will appear below the ribbon. Here, you'll enter your DAX expression. The format is a new column Name = FUNCTION(TableName[ColumnName]). For our example, if our table is named Sales and the date column is OrderDate, the formula would be:

Year = YEAR(Sales[OrderDate])

  1. Press Enter: Hit the Enter key to confirm the formula. A new column named "Year" will be added to your table, populated with the corresponding year for each row's OrderDate.

Option B: The FORMAT() Function

The FORMAT() function is a versatile Swiss Army knife in DAX for changing how data is displayed. It can also be used to extract the year, but with one important difference: it returns the year as a text string, not a number.

Here's how you'd use it to create a new calculated column:

Year (as text) = FORMAT(Sales[OrderDate], "YYYY")

When to Use FORMAT()? And When Not To?

  • Use it for display: If your sole purpose is for labeling visuals and you don't need to perform any numerical sorting or filtering (e.g., "show me years after 2021"), FORMAT() is fine.

  • Avoid it for analytics: When you format the year as text, Power BI might sort it alphabetically (2022, 2023, 2024 is okay, but if you have a decade, it could sort as 2019, 2020, 2021..., 2029, 2030, a common problem!) instead of numerically. Moreover, you can't use it in mathematical calculations. For these reasons, the YEAR() function is almost always the better choice.

A Quick Note on Using a Date Table

As you get more advanced with Power BI, you'll learn that a best practice is to create a dedicated Date Table (or Calendar Table). A Date Table is a separate table in your model that contains one row for every single day in your data's timeframe and has columns for attributes like Year, Quarter, Month Name, Day of the Week, etc.

By creating this central table, you can relate it to the date columns in your other tables (like sales, marketing, and operations). This has several advantages:

  • It's efficient: You create Year, Month, and Quarter columns once, in one place, instead of adding them to every single table in your model.

  • It's consistent: All your time-based analytics are driven from a single source of truth for dates.

  • It enables Time Intelligence: A proper Date Table is required for many powerful DAX time intelligence functions, such as SAMEPERIODLASTYEAR() used for YoY comparison.

Even a basic Date Table removes the need to add a "Year" column to your fact table directly. You would instead just drag the "Year" column from your Date Table into your visuals and slicers.

Putting It Into Practice: Examples

Once you have your new 'Year' column, here's how you might use it in a report:

1. Creating a Year Slicer

A slicer is a type of on-canvas visual filter. To let your users filter your report by year:

  • Click on the Slicer icon in the Visualizations pane.

  • Drag your newly created Year column into the 'Field' well of the slicer options.

  • By default, it may appear as a slider. You can change this under 'Format your visual' → 'Slicer settings' → 'Style' to be a Vertical list or Dropdown for easier selection.

2. Showing an Annual Sales Trend

To visualize how your sales have performed over the years:

  • Add a Line chart or Column chart to your report canvas.

  • Drag your Year column to the X-axis.

  • Drag your sales metric (e.g., 'Total Revenue') to the Y-axis.

This instantly creates a clear visual showing your revenue trend for each year represented in your data.

Final Thoughts

Extracting the year from a date column is a gateway skill in Power BI that opens up a world of more meaningful trend analysis and reporting. Whether you prefer the straightforward clicks in the Power Query Editor or the flexibility of writing a DAX calculated column with the YEAR() function, mastering this technique is essential for building effective and user-friendly dashboards.

Of course, the reporting process still involves quite a few manual steps, from connecting data and transforming it to writing formulas and building each chart. We designed Graphed to simplify this entire workflow. Instead of going through multiple steps to add a year column and then build a chart, with our platform, you can just ask in plain language, "Show me a line chart of my revenue by year from my Shopify data." We build the live dashboards for you in seconds, saving you time usually spent in Power Query or writing DAX.