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]))

Follow up learning

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