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 here.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI:
How to Convert Date to Text in Power BI
How to Count Number of Occurrences in Power BI
How to Use “If Contains” in Power BI
