Table of Contents
To convert a date to month and year format in Power BI, you can use the FORMAT function. First, create a new column or measure and use the FORMAT function, specifying the date column as the first argument and the desired format as the second argument. For example, if the date column is named “Date,” the formula would be FORMAT(Date,”MM-yyyy”) to display the date in month and year format. This function will convert the date into a text value, so make sure to change the data type of the new column or measure to “Text” to avoid any errors.
You can use the following formulas in DAX to convert dates to month and year formats in Power BI:
Formula 1: Convert Date to Month and Full Year (e.g. 01/2022)
month_year = FORMAT('my_data'[Date], "MM/YYYY")
Formula 2: Convert Date to Month and Last Two Digits of Year (e.g. 01/22)
month_year = FORMAT('my_data'[Date], "MM/YY")
Formula 3: Convert Date to Abbreviated Month and Full Year (e.g. Jan. 2022)
month_year = FORMAT('my_data'[Date], "MMM. YYYY")
Formula 4: Convert Date to Full Month and Full Year (e.g. January 2022)
month_year = FORMAT('my_data'[Date], "MMMM YYYY")
The following examples show how to use each method in practice with the following table in Power BI:
Example 1: Convert Date to Month and Full Year
To convert a date to month and full year, click the Table tools tab, then click the icon called New column:
Then type the following formula into the formula bar:
month_year = FORMAT('my_data'[Date], "MM/YYYY")
This will create a new column named month_year that displays the corresponding dates in the Date column as a month and full year:
Example 2: Convert Date to Month and Last Two Digits of Year
To convert a date to month and last two digits of a year, click the Table tools tab, then click the icon called New column:
Then type the following formula into the formula bar:
month_year = FORMAT('my_data'[Date], "MM/YY")
This will create a new column named month_year that displays the corresponding dates in the Date column as a month and last two digits of the year:
Example 3: Convert Date to Abbreviated Month and Full Year
To convert a date to an abbreviated month name and full year, click the Table tools tab, then click the icon called New column:
Then type the following formula into the formula bar:
month_year = FORMAT('my_data'[Date], "MMM. YYYY")
This will create a new column named month_year that displays the corresponding dates in the Date column as an abbreviated month name and full year:
Example 4: Convert Date to Full Month and Full Year
To convert a date to a full month name and full year, click the Table tools tab, then click the icon called New column:
Then type the following formula into the formula bar:
month_year = FORMAT('my_data'[Date], "MMMM YYYY")
This will create a new column named month_year that displays the corresponding dates in the Date column as a full month name and full year:
Note: You can find the complete documentation for the FORMAT function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: