How to add a year column to a Power BI table using DAX?

by

Create a year column in Power BI

You can add a year variable to your BI data model by following this process:

  • Open your Power BI Report (pbix file).
  • Open the Data View.
  • In the Data View, select your table from the BI model.
  • Hit on the Next Column column.
  • Use the Year DAX formula to get the year column. Type the following code into your formula bar – modify this code as needed to suit your needs:
Open Year = Year( Courses [Open Date])
  • Save your Power BI model.

Format date / time column as year

To format a Power BI date value as a year column use the following process:

  • In the Power BI Table View , select your table.
  • In the formula bar, type the following DAX statement:
Open Year = FORMAT(Courses[Open Date], "yyyy")
  • Don’t forget to save your model.

Extract the year and month values from a date column

Here’s the syntax you can use to add a new column that combines the year and month values:

Open Year-Month = YEAR(Courses[Open Date]) & "-" & MONTH(Courses[Open Date])

Here is a screenshot of your table:

Add a year column with DAX

Proceed as following to add a column to your model in DAX:

  • Open your preferred DAX editor (i use DAX Studio).
  • User the YEAR DAX function to obtain the year values.
  • Add your year column using the DAX ADDCOLUMNS function. In our case we will extract the course open year into the Open Year column. 
EVALUATE
ADDCOLUMNS( Courses , "Open Year" , YEAR(Courses[Open Date]))

Additional Questions

Are there performance considerations when adding a year column?

Based on my experience, when adding DAX calculated columns to small datasets (up to couple hundred thousand rows) there is no visible impact. However, as the dataset size scales up, performance might become an issue. If your dataset size comprises more than a million rows, consider creating your year co,umn in your data source (using SQL).

Can i change my year column format?

Yes, from the Power BI Model, Report and Data views you can change your year column format:

  • select your year column in the right hand side Fields pane
  • Customize your display formart as needed to mmyyyy, mmyy, yy or as otherwise needed.

Can i create a column showing my fiscal year?

Yes you can use a conditional IF statement or the SWITCH function to derive the fiscal year from your date or month column.

Follow up learning

How to verify if a Power BI model contains a specific string?