Date columns in Power BI are generally recorded in Date or Datetime formats. which means the day, month, and year (and time) are in one column in dd-mm-yyyy or similar formats. In case we need one element from the date column we need to extract it from the column.
We can do that in Power Query Editor or using DAX custom columns.
In this post, we discuss a few methods we can use.
Extracting Month & Year from a Date in Power Query
for this example, we use a simple one-column dates table.
Using Power Query M Functions to Get the month from date.
Go to Add Column >> Custom Column
On the next window add this formula and add the Date column as the first property.
You will get a number column you can convert to whole number data type and it carries the month component of the date column.
= Date.Month( [Date] )
Use the DAX custom column to Get the month from date.
In this method, we can use an existing date column to create a month column with an extracted month value.
Create a new column and add the below formula.
Month = MONTH( 'Calendar'[Date] )
Using Power Query M Functions to Get the month name from the date.
Go to Add Column >> Custom Column
On the next window add this formula and add the Date column as the first property.
= Date.MonthName ( [Date] )
You will get a number column you can convert to whole number data type, and it carries the month component of the date column.
The good thing about getting the months in whole numbers is that later when you want to sort by month you can get the correct month order.
Use the DAX custom column to Get the month name from the date.
There is no DAX function to get the month name directly out of the DAX column like MONTH ( ). Instead, we need to use the FORMAT ( ) function.
Create a new column and add the below formula.
Month Name = FORMAT( 'Calendar'[Date] , "mmmm" )
Using Custom Columns to Get Year Out of Particular Date in Power BI
Go to Add Column >> Custom Column
On the next window add this formula and add the Date column as the first property.
= Date.Year ( [Date] )
You will get a number column you can convert to a whole number data type, and it carries the year component of the date column.
Using Power Query Editor to Extract Quarter from a Date
Go to Add Column >> Custom Column
On the next window add this formula and add the Date column as the first property.
= Date.QuarterOfYear ( [Date] )
You will get a number column you can convert to whole number data type, and it carries the quarter component of the date column.
This column shows one of 4 number one for each column, but when we show quarters in your reports don’t show it like that. generally, the quarter number is associated with some character like Q or Qtr. Let’s see how we can prefix some text to the quarter number column as a prefix.
Open up the customer column screen.
we need to concatenate the text Q (or any other) with the return value of this formula (quarter of the year).
The return value of the Date.QuarterOfYear ( ) is a whole number, we can’t concatenate it with a text value in its raw form. We need to convert it to text first.
Here is the formula. this will convert quarter numbers to text.
= Text.From( Date.QuarterOfYear ( [Date] )
Then use the & operator to concatenate.
= "Q" & Text.From( Date.QuarterOfYear ( [Date] ) )
here is the new column.
Using DAX custom column to Extract Quarter from a Date
We can do this by just using the DAX FORMAT ( ) function.
Create a new column and add the below formula.
Quarter = FORMAT( 'Calendar'[Date] , "Q" )
To join a text to the quarter number (Q r Qtr) we can use another DAX function.
Here is the function.
Quarter = CONCATENATE("Q" , FORMAT( 'Calendar'[Date] , "Q" ) )
Useful Links.
Select the relative dates (Weeks, Months, Years) from a slicer. – overbeeps