Power BI: Extract Month & Year From Date

Working with date columns to customize your visualizations is a very common issue in data analysis. For any of the following methods to work, ensure that the data column you’ll be using is of Date type.

Let’s say we have a date column such as this:

There are multiple ways we can manipulate the date column.

Extract Day, Week, Quarter, Month or Year from Date using DAX

Using DAX:

DAX has functions that you can use to extract the day, month, quarter and year from the date.

start_day = day(DATA[start_date])

start_month = MONTH(DATA[start_date])

start_quarter = QUARTER(DATA[start_date])

start_year = year(DATA[start_date])

Using Power Query Editor:

You can extract any part of the date as a new column in the Power Query Editor. Follow these steps:

1. From Add Column, open the Date dropdown
2. From the dropdown, select the option you’d like from year, month, quarter, week, or day
3. You can also get other option such as Start of Year, End of Year, Quarter of Year, Week of Year, Week of Month, Day, Day of Week, Day of Year, Name of Day and more.

Extract Day, Month or Year from Date using Power Query M

1. Go to Add Column > Custom Column
2. In the popup, enter the code Date.Year([start_date]) to get the Year where [start_date] is the name of the column with the dates.

Date.Year([start_date])

Other functions available in Power Query M are:

• Date.Year()
• Date.Month()
• Date.WeekOfMonth()
• Date.WeekOfYear()
• Date.Day()

Format Date to Year-Month (YYYY-MM) Format

You can use the FORMAT() function to format a date in any form you’d like. In the Table View > Column Tools > New column, enter the DAX code below:

FORMAT(Value, Format, [LocaleName])

The FORMAT functions converts a value to text in the specified number format. Place the format value in quotes.

formatted_date = FORMAT(DATA[start_date], "YYYY-MM")

This will convert a date to the format, for example, 2022-10

formatted_date = FORMAT(DATA[start_date], "YYYY-MMM")

This will give a date as 2022-Oct

formatted_date = FORMAT(DATA[start_date], "YYYY-MMM")

This will give a date as 2022-October.