Power BI: How to 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:

Power BI convert date to month and 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

Leave a Reply

Your email address will not be published. Required fields are marked *