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)
power bi date function
power bi date function

*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:

Date function example on Power BI
Date function example on Power BI

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:

Date function example on Power BI
Date function example on Power BI

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:

Power BI DAX date function
Power BI DAX date 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])
DAX date function in Power BI
DAX date function in Power BI

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])
Example of DAX date function in Power BI
Example of DAX date function in Power BI

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])
DAX Date function in Power BI
DAX Date function in Power BI

Read Power BI Measure Date

See also  How to Create PDF from Excel using Power Automate

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])
DAX DATE- Hour function example on Power BI
DAX Date-Hour function example on Power BI

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])
Date-minute function in Power BI using DAX
Date-minute function in Power BI using DAX

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])
DAX Date-second function in Power BI
DAX Date-second function in Power BI

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

TODAY()

For example:

DAX date function example in Power BI
DAX date function example in Power BI

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]))
Example DAX date function in Power BI
Example DAX date function in Power BI

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)
DAX week-Date function in Power BI
DAX week-Date function in Power BI

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)
DAX week-Date function in Power BI
DAX week-Date function in Power BI

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:

Example of Date function in Power BI
Example of a Date function in Power BI

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:

Date function in Power BI
Date UTC function in 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])
Date-Year function in Power BI
Date-Year function in Power BI

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)
Date DAX function in Power BI
Date DAX function in Power BI

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

See also  How to format a currency column in Power Apps?

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)
Date function using DAX in Power BI
Date function using DAX in Power BI

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])
Date function using DAX in Power BI
Date function using DAX in Power BI

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))
Calendar date function in Power BI
Calendar date function in Power BI

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:

Calendar date function in Power BI
Calendar date function in Power BI

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.

FunctionDescriptionSyntax
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)
Date Function using PowerQuery M

Read Power bi treemap – How to use

See also  Stacked Column Chart in Power BI

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.

date function using M query
date function using M query

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)
Date.AddDays() function using M query
Date.AddDays() function using M query

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])
Date.Days() function using M query
Date.Days() function using M query

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])
Date.Year() function using M query
Date.Year() function using M query

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])
Date.IsInCurrent() function using M query
Date.IsInCurrent() function using M query

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])
Date.IsInNextMonth() function using M query
Date.IsInNextMonth() function using M query

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])
Date.EndOfMonth() function using M Query
Date.EndOfMonth() function using M Query

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])
Date.IsLeapYear() function using M query.png
Date.IsLeapYear() function using M query.png

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)
Date.AddYears() function using M Query
Date.AddYears() function using M Query

Example-9: Using Date.DaysInMonth() function

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

Days in month = Date.DaysInMonth([Join Date])
Date.DaysInMonth() function using M Query
Date.DaysInMonth() function using M Query

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")
Date.MonthName() function using M
Date.MonthName() function using M

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])
Date.QuarterOfYear() function using M
Date.QuarterOfYear() function using M

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])
Date.IsInPreviousMonth() function using M
Date.IsInPreviousMonth() function using M

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])
Date.IsInCurrentQuarter() function using M
Date.IsInCurrentQuarter() function using M

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)
Date.IsInPreviousNMonths() function using M-query
Date.IsInPreviousNMonths() function using M-query

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")
Date.ToText() function using M
Date.ToText() function using M

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

You may like the following Power BI tutorials:

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.
>