Power BI Date Function with Examples

In this Power BI Tutorial, we will learn about Power BI Date functions. Also, we will discuss:

• Power BI Date functions
• Power BI DAX date function examples
• Power BI date function M query
• Examples of Date function using PowerQuery M

Power BI Date functions

There are various types of built-in date functions available on Power BI. It returns the specified date in DateTime format.

The syntax is:

Date(year,month,day)

The Year represents a number that represents a particular year. It supported the dates beginning with March 1, 1990. If we enter a number greater than 9999 and less than zero or a negative number, the function returns an #Value! error.

The Month is represented by a number, which is a numerical value of a month name. i.e. for January, it is 1, for February it is 2,….etc. It does not support the negative value. The value must be between 1-12. If we enter an integer greater than 12, it is calculated by adding the value of the month to the year.

The Day is represented as a number. The number should be between 1-31. If a day contains a decimal value, then it is calculated to be rounded as the nearest integer value.

Example-1: working with year

We have prepared an employee table having their name, joining date, and leaving date. Now we will create a date column that showing a particular date in a specified data column.

For example, we will create a date column that returns the date June 14, 2021.

Date(2021,6,14)

*If we use the year value between 1900-9999, then it is used as the year. If the value is before 1900 i.e. 1899, then the value is added 1900 to calculate the year.

Example-2: Working with the month

If we enter the month value between 1-12, it returns as a month. If a month is greater than 12, then the month adds the number of months to the first month in the specified year.

Let’s have a look at the below example:

Here month 16 is calculated as 12+4. 4 is represented as April.

Example-3: Working with day

Similarly, If we enter the day value between 1-31, it returns as a day. If a month is greater than 12, then the day adds the number of days to the first day in the month.

Let’s have a look at the below example:

This is how to work with Power BI DAX date functions.

Read How to Embed Power BI Report in SharePoint Online

Power BI DAX date function examples

Now we will discuss the other various date functions using the DAX expression available on Power BI with examples:

NOW() – This Power BI DAX function returns the current date and time. The syntax of this date function is:

NOW()

Example of Now DAX function:

DAY() – It represents the number from 1-31 representing the day of the month. For this function the syntax is:

DAY(DATE)

Here is an example, by applying this function the days are coming as a number.

Day = DAY(Employee[Join Date])

MONTH() – This function returns a number from 1(January) to 12(February) representing the month of the year. The syntax is:

MONTH(DATE)

Let’s create a column that returns the month as a number.

Month = MONTH(Employee[Leave Date])

YEAR() – This DAX function returns the year of date as a four-digit number. The syntax is:

YEAR(DATE)

To implement this function here we will create a column that returns the year of the employee’s join date.

Joining year = YEAR(Employee[Join Date])

Read Power BI Measure Date

HOUR() – This function returns the hour as a number i.e. 0(12.00AM) to 23(11.00 PM). For this syntax is:

HOUR(DATETIME)

We can see the below example to implement this function:

Hour = HOUR('Employee Table'[Today])

We can see that the hour column returned as 19 which means 7 PM(according to today’s column).

MINUTE()- This function returns a number from 0 to 59 representing the minutes. The syntax is:

MINUTE(DATETIME)

Let’s create a column that shows the minute of a date-time i.e Today column.

Minute = MINUTE('Employee Table'[Today])

SECOND() – It returns a number from 0 to 59 representing the second. For this the syntax is:

SECOND(DATETIME)

Here is an example have a look at it:

SECOND = SECOND('Employee Table'[Current Date-time])

TODAY() – This function returns the current date in date-time format. For this the syntax is:

TODAY()

For example:

We can see the difference between the Current Datetime column(where we used the NOW() function) and the Today column(where we used the TODAY() function).

TIME() converts hours, minutes, and seconds given as numbers to a time in DateTime format. The Syntax is:

TIME(Hour,Minute,Second)

Here we add a new column to implement this TIME():

Time = TIME(HOUR('Employee Table'[Current datetime]),MINUTE('Employee Table'[Current datetime]),SECOND('Employee Table'[Current datetime]))

Read Countif function in Power BI Measure

WEEKDAY() – It returns a number from 1 to 7 identifying the day of the week of a date, where 1 is for Sunday and 7 is Monday. There are 3 types of return types available:

• 1 – Sunday=1 through Saturday=7
• 2 – Monday=1 through Sunday=7
• 3 – Monday=0 through Sunday=6

The syntax is:

WEEKDAY(Date,[Return Type])

Here is an example to implement this function:

Week day = WEEKDAY('Employee Table'[Join Date],1)

WEEKNUM() – This function returns the week number in the year. There are 2 types of return types.

• 1- Week begins on Sunday
• 2- Week begins on Monday

For this the syntax is:

WEEKNUM(DATE,[Return type])

Let’s create a column that shows the week in a number:

weekinNumber = WEEKNUM('Employee Table'[Join Date],2)

UTCNOW() – This function returns the current date and time in the DateTime format expressed in Coordinated Universal Time(UTC). For this the syntax is:

UTCNOW()

Here we will create a column that displays the date-time in UTC format like this:

UTCTODAY()- Similarly, this function returns the current date in the DateTime format expressed in Coordinated Universal Time(UTC). The syntax is:

UTCTODAY()

Here is an example of this function on Power BI:

YEARFRAC()– This function is used to return the year fraction representing the number of whole days between StartDate and EndDate. The syntax is:

YEARFRAC(Start_Date,End_Date,[Basic])

Here is an example:

year = YEARFRAC('Employee Table'[Join Date],'Employee Table'[Leave Date])

EDATE() – It returns the date that is the indicated number of months before or after the start date. The syntax of this function is:

EDATE(StartDate, Month)

Let’s have a look at this below example:

Edate = Edate('Employee Table'[Join Date],5)

In the above screenshot, the Edate column shows the dates after 5 months of the Join date.

DATEDIFF() – It returns the number of units between the input of two dates. The syntax is:

DATEDIFF(Date1, Date2, Interval)

Here we will show the date difference between the join date and today’s date in the year:

Date-difference = DATEDIFF('Employee Table'[Join Date], NOW(), YEAR)

DATEVALUE() – It converts the date in the form of text to date in DateTime format. The syntax is:

DATEVALUE(DateText)

Have a look at the below example:

date value = DATEVALUE('Employee Table'[Join Date])

CALENDAR()– It returns a table with one column of all dates between the start date and end date. The syntax is:

CALENDAR(StartDate, EndDate)

Here we will create a table having dates from July 13 to July 16 of 2021. The table will look like this:

Table = CALENDAR(DATE(2021,7,13), DATE(2021,7,18))

CALENDARAUTO() – It returns a table with one column of dates calculated from the model automatically. For this the syntax is:

CALENDARAUTO()

here is an example of the CALENDARAUTO() function:

Read Power bi slicer filter another slicer 

Power BI date function M query

In Microsoft Power BI, the date component of date, DateTime, and date timezone values are created and manipulated by the date function using the M query.

We can use this date function by adding a custom column on Power BI Query Editor.

Here we will describe all the functions, their description, and their syntax as well.

Function	Description	Syntax
Date.AddDays()	It returns a date, date-time, or date time zone value with the day section incremented by the number of days that we provided.	Date.AddDays(DateTime as any, NumberofDays as number)
Date.AddMonths()	This function returns a DateTime value with the month section incremented by n months.	Date.AddMonths(DateTime as any, NumberofMonths as number)
Date.AddQuarters()	It returns a date,date-time, or date time zone value incremented by the number of the quarter that provided. There are 4 quarters and each quarter is divided by 3 months.	Date.AddQuarters(dateTime as any, numberOfQuarters as number)
Date.AddWeeks()	It returns a date,date-time, or date time zone value incremented by the number of weeks provided. Each week is divided into 7 days.	Date.AddWeeks(DateTime as any, NumberOfWeeks as number)
Date.AddYears()	This function returns a DateTime value with the year section incremented by n years.	Date.AddYears(DateTime as any, NumberofYear as number)
Date.Day()	It returns the day for a DateTime value.	Date.Day(dateTime as any)
Date.DayOfWeek()	It returns a number(0-6) that representing the day of the week provided.	Date.DayOfWeek(dateTime as any, firstDayOfWeek as nullable number)
Date.DayOfWeekName()	It returns the day of the week name.	Date.DayOfWeekName(date as any, culture as nullable text)
Date.DayOfYear()	It returns a number from 1 to 366 indicating the day of the year.	Date.DayOfYear(dateTime as any)
Date.DaysInMonth()	This function returns a number from 28 to 31 indicating the number of days in the month.	Date.DaysInMonth(dateTime as any)
Date.EndOfDay()	It returns the last value of the day in DateTime format.	Date.EndOfDay(dateTime as any)
Date.EndOfMonth()	It returns the last value of the month in DateTime format.	Date.EndOfMonth( dateTime as any)
Date.EndOfQuarter()	It returns the last value of the quarter in DateTime format.	Date.EndOfQuarter(dateTime as any)
Date.EndOfWeek()	It returns the last value of the week in DateTime format.	Date.EndOfWeek(dateTime as any)
Date.EndOfYear()	It returns the last value of the year in DateTime format.	Date.EndOfYear(dateTime as any)
Date.Form()	It creates a date from the given value.	Date.From(value as any, optional culture as nullable text)
Date.FromText()	It creates a Date from local and universal Date formats.	Date.FromText(text as nullable text, culture as nullable text)
Date.IsInCurrentDay()	It indicates whether this date occurs during the current day, as determined by the current date and time on the system.	Date.IsInCurrentDay(dateTime as any)
Date.IsInCurrentMonth()	It indicates whether this date occurs during the current month, as determined by the current date and time on the system.	Date.IsInCurrentMonth(dateTime as any)
Date.IsInCurrentQuarter()	It indicates whether this date occurs during the Current Quarter, as determined by the current date and time on the system	Date.IsInCurrentQuarter(dateTime as any)
Date.IsInCurrentWeek()	It indicates whether this date occurs during the current week, as determined by the current date and time on the system	Date.IsInCurrentWeek(dateTime as any)
Date.IsInCurrentYear()	It indicates whether this date occurs during the current year, as determined by the current date and time on the system	Date.IsInCurrentYear(dateTime as any)
Date.IsInNextDay()	It indicates whether this date occurs during the next day, as determined by the current date and time on the system.	Date.IsInNextDay(dateTime as any)
Date.Month()	Returns the month component.	Date.Month(dateTime as any)
Date.StartOfDay()	Returns the first value of the day.	Date.StartOfDay(dateTime as any)

Read Power bi treemap – How to use

Examples of Date function using PowerQuery M

From these above functions, now we will see how it works with some examples.

Example-1: Using Date.AddDays() function.

Here we have an employee table having their name and 1st vaccination of Covid.

Now we will calculate the day for 2nd vaccination, which will come after 28 days.

On Power Query Editor > Add column > Custom column

2nd vaccination = Date.AddDays([1st vaccination],28)

Example-2: Using of Date.Days() function

In the below example, we extract the days from the joining date of the employee using this function.

Day of Joining = Date.Day([Join Date])

Example-3: Using Date.Years() function

Similarly, we can extract the year from the joining date of the employee by using this function:

Year of Joining = Date.Year([Join Date])

Example-4: Using Date.IsInCurrentDay() function

In this function, it returns True if the date-time matches with the system’s current date and time otherwise it returns False.

IsInCurrentDay = Date.IsInCurrentDay([2nd vaccination])

Example-5: Using Date.IsInNextMonth() function

Similarly, it returns True if the Month matches with the system’s current date and time otherwise it returns False.

Is IN Next Month = Date.IsInNextMonth([2nd Vaccination])

Example-6: Using Date.EndOfMonth() function

It returns the ending date of the Employee’s Leave date in a date-time format like below:

End Of Month = Date.EndOfMonth([Leave Date])

Example-7: Using of Date.IsLeapYear() function

It returns the True if the year is a leap year otherwise it returns False.

Is it leap year = Date.IsLeapYear([Join Date])

Example-8: Using Date.AddYears() function

Here we calculate another 3 years of the joining date of the employee.

Bond Year = Date.AddYears([Join Date],3)

Example-9: Using Date.DaysInMonth() function

It returns the number of days in a month like below:

Days in month = Date.DaysInMonth([Join Date])

Example-10: Using Date.MonthName() function

It returns the month name of the joining date like this:

Joining Month = Date.MonthName([Join Date], "en-US")

Example-11: Using Date.QuarterOfYear() function

It returns a number from 1 to 4 representing the quarter of the year like this:

Quarter no. = Date.QuarterOfYear([Join Date])

Example-12: Using Date.IsInPreviousMonth() function

It indicating whether the given DateTime occurs during the previous month according to the current date and time of the system.

Is In Previous Month= Date.IsInPreviousMonth([Join Date])

Example-13: Using Date.IsInCurrentQuarter() function

By using this function we can evaluate whether the date is in the current quarter or not. If yes then it comes as True otherwise it comes as False. For example:

Leave date is in current quarter = Date.IsInCurrentQuarter([Leave Date])

Example-14: Using Date.IsInPreviousNMonths() function

It returns True if the given date-time befalls during the previous no. of months, as determined by the current DateTime on the system. It returns False when passed a value that occurs within the current month.

Is in Previous N month = Date.IsInPreviousNMonths([Leave  Date],5)

Example-15: Using Date.ToText() fuction

By using this function we can represent the DateTime into a textual format i.e. “yyyy/MM/dd” like this:

Join Date format = Date.ToText([Join Date],"yyyy/MM/dd")

This is how we can use these M Query Date functions on Power BI.

You may like the following Power BI tutorials:

• Power BI Slicer Dropdown
• How to use Power bi ribbon chart
• Power BI Slicer Buttons
• Power BI Q&A – How to use with examples
• Power BI Filter vs Slicer
• Power BI Measure Filter

Conclusion

From this Power BI Tutorial, we learned about Power BI Date Functions. Also, we discussed:

• Differents types of Date functions available on Power BI.
• Discussed about Power BI DAX Date function with examples.
• What are the Power BI date function M query?
• Examples of Date function using PowerQuery M.