Table of Contents
To convert a date to month and year format in Power BI, you can use the DAX function “FORMAT” and specify the desired format using the “MMM-YYYY” code. This will display the date in a shortened month name followed by the full year, such as “Jan-2020”. This format can be applied to any date column or measure in your Power BI report, allowing for easy analysis and visualization of data by month and year. Additionally, you can use the “DATETIME” function to convert a date column into a date/time format, allowing for further customization and analysis.
Power BI: Convert Date to Month and Year Format
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: