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?