Power BIPowerBI tutorials

Power BI DAX date functions

Power BIPowerBI tutorials

Power BI DAX date functions

Power BIPowerBI tutorials

Power BI DAX date functions

How to use Power BI DAX date functions with examples? Microsoft Power BI DAX provides various date functions such as year, month, day, calendar, date, time, dateiff, now, today, utcnow, utctoday, datevalue, timevalue, etc.

To demonstrate these Power BI DAX Date features, we used the data shown below. As you can see, there are 15 records in this table.

power-bi-dax-date-functions-1-7298587

Power BI DAX date functions

The following series of examples shows the list of DAX date functions in Power BI

Power BI DAX NOW feature

The Power BI DAX Now function is one of the Power BI date functions, used to return the current date and time. The syntax for this Power BI DAX NOW is

NOW ()

To demonstrate the functions of Power BI DAX NOW, we have to use the Calculated column. To create a column, click the New column option under the Home tab, or Modeling tongue.

power-bi-dax-date-functions-2-2668610

We rename the default column name as Today. As you can see in the screenshot below, while typing, Power BI displays the suggestions.

power-bi-dax-date-functions-3-9376601

Clicking Enter or any key creates a new column.

power-bi-dax-date-functions-4-3100626

Let me add this column to the table we created earlier. See the Create Table Report article to understand the steps required to create a table.

power-bi-dax-date-functions-5-8230769

As you can see from the screenshot above, it shows today's date and time.

Power BI DAX DAY function

The DAY function in Power BI DAX extracts or returns the day number of the specified date. The syntax for the Power BI DAX DAY function is

DAY (Date)

Let me create a column to return the day number from the Hire Date column

DayinDate = DAY (EmployeeSales [HireDate])
power-bi-dax-date-functions-6-3281288

Power BI DAX MES function

The Power BI DAX month function returns the number of the month as of a specified date. The syntax for the Power BI DAX MONTH function is

MONTH (Date)

Let me create a column to return the month number from the Hire Date column

MonthinDate = MONTH (EmployeeSales [HireDate])

Here, we add the result of a previous function (day function) to the Table.

power-bi-dax-date-functions-7-6639266

Power BI DAX YEAR function

The Power BI DAX YEAR function extracts or returns the year number from the specified date. The syntax of this Power BI DAX YEAR:

YEAR (Date)

Let me create a column to return the year from the hire date column

YearInDt = YEAR (EmployeeSales [HireDate])
power-bi-dax-date-functions-8-3528892

Power BI DAX HOUR function

The TIME function in Power BI DAX returns Hours (in 24-hour format) since a specified date. The syntax for the Power BI DAX HOUR function is:

HOUR (DateTime)

Let me create a column to return the hours from the hire date column

Hour = HOUR (EmployeeSales [HireDate])
power-bi-dax-date-functions-9-3665215

MINUTE function of Power BI DAX

The Power BI DAX MINUTE function returns the number of minutes. The syntax of this Power BI DAX MINUTE is:

MINUTE (DateTime)

Let me create a column to return the number of minutes from the Hire Date column

Min = MINUTE (EmployeeSales [HireDate])
power-bi-dax-date-functions-10-4700845

Power BI DAX SECOND function

The SECOND function in Power BI DAX returns the number of seconds. The syntax of this Power BI DAX SECOND is:

SECOND (DateTime)

Let me create a column to return the number of seconds in the Hire Date column

Sec = SECOND (EmployeeSales [HireDate])
power-bi-dax-date-functions-11-1583272

Let me add the results of the Year, Month, Day, Hour, Minute, and Second functions to the table.

power-bi-dax-date-functions-12-6935550

Power BI DAX TODAY function

The Power BI DAX Today function returns today's date with the default system time. The syntax for this Power BI DAX Today is:

TODAY ()

Let me create a column to display today's date.

Today 2 = TODAY ()
power-bi-dax-date-functions-13-7619269

Let me compare NOW () and TODAY ().

In the screenshot below, you can see that there is a time difference between the two.

power-bi-dax-date-functions-14-4383108

Power BI DAX DATE function

The Power BI DAX Date function is useful for returning the date of the year, month, and day. The syntax for the Power BI DAX Date function is:

DATE (year, month, day)

Let me create a single column to create a date from the year, month and day

Date = DATE (YEAR (EmployeeSales [HireDate]), MONTH (EmployeeSales [HireDate]), DAY (EmployeeSales [HireDate]))
power-bi-dax-date-functions-17-8708226

Power BI DAX TIME function

The time function in Power BI DAX returns the time from the specified hour, minute, and second. The syntax for this Power BI DAX time function is:

TIME (hour, minute, second)

Let me create a column to create the time from the hour, minutes, and seconds.

Time = TIME (HOUR (EmployeeSales [HireDate]), MINUTE (EmployeeSales [HireDate]), SECOND (EmployeeSales [HireDate]))
power-bi-dax-date-functions-18-7597125

Let me add the result of the Power BI DAX Time function to the table report. If you find the output as the default date with time, change the data type from date / time to time.

power-bi-dax-date-functions-19-7155079

Power BI DAX day of week feature

The Power BI DAX Weekday function returns numbers from 1 to 7, where 1 from Sunday and 7 from Monday. The Power BI DAX Weekday function syntax is: The Power BI DAX Weekday function syntax is:

WEEKDAY (Date, Number)
  • If Number = 1 then 1 from Sunday
  • If Number = 2 then 1 from Monday
  • and Number = 3 then 0 from Monday

Let me create a column to find the number of the day of the week.

WeekDay = WEEKDAY (EmployeeSales [HireDate], 1)
power-bi-dax-date-functions-22-2422382

You can see the numbers of the week

power-bi-dax-date-functions-23-6153699

Power BI DAX WeekNum function

The Power BI DAX WeekNum function returns the week number (1 as the first week of January). The syntax for this Power BI DAX Weeknum function is:

WEEKNUM (Date, Number)

If Number = 1 The week starts on Sunday and if Number = 2, the Week starts on Monday. Let me create a column to find the number of the week.

WeekNum = WEEKNUM (EmployeeSales [HireDate], 1)
power-bi-dax-date-functions-24-1411062

Power BI DAX UTCNOW function

The DAX UTCNOW function is the same as the Power BI DAX NOW function. However, it returns the date and time along with UTC. The syntax for this Power BI DAX UTCNOW function is:

UTCNOW ()

Let me create a column to find the date and time along with UTC

UTCNow = UTCNOW ()
power-bi-dax-date-functions-25-6086461

Power BI DAX UTCTODAY function

The Power BI DAX UTCTODAY function is the same as the ODAY function. However, it returns the date with the default time along with UTC. The syntax of this Power BI DAX UTCTODAY is:

UTCTODAY ()

Let me create a column to find the date with UTC

UTCToday = UTCTODAY ()
power-bi-dax-date-functions-26-1852318

Power BI DAX YEARFRAC function

The Power BI DAX YEARFRAC function returns the fractional difference between the start date and the end date. The Power BI DAX YEARFRAC syntax is:

YEARFRAC (Start_Date, End_Date)

Let me find the exact number of years difference between the hire date and today

YearFrac = YEARFRAC (EmployeeSales [HireDate], NOW ())
power-bi-dax-date-functions-27-4788488

Power BI DAX EDATE function

The Power BI DAX EDATE function returns earlier dates or future dates. For example, the expiration date of the policy adding 24 as the second argument, etc. The syntax for this Power BI DAX EDATE function is:

EDATE (Start_Date, Months)

Let me add 2 months to the hire date

Edate = EDATE (EmployeeSales [HireDate], 2)
power-bi-dax-date-functions-28-5147438

Power BI DAX EOMONTH function

The Power BI DAX EOMONTH function (EOMONTH means the end of the month) displays the last day of a month. You can use the second argument to return earlier or future dates. The syntax of this Power BI DAX EDATE is:

EOMONTH (Start_Date, Months)

Let me add 3 months to the hire date. The following statement first adds three months to the hire date and then returns the last day of that month.

Eomonth = EOMONTH (EmployeeSales [HireDate], 3)
power-bi-dax-date-functions-29-2466372

Power BI DAX DATEDIFF function

The Power BI DAX DATEDIFF function returns the date difference between the start date and the end date. You can use the third argument of the Power BI DAX DATEDIFF function to select the difference value.

The Power BI DAX DATEDIFF function accepts YEAR, MONTH, DAY, SECOND, MINUTE, HOUR, QUARTER, WEEK. For example, if you select MONTH, the DateDiff function returns the number of the month difference between a start date and an end date.

The syntax for this Power BI DAX DATEDIFF function is:

DATEDIFF (Start_Date, End_Date, Difference_Value)

Let me check the difference between the hire date and today in years.

DateDiff = DATEDIFF (EmployeeSales [HireDate], NOW (), YEAR)
power-bi-dax-date-functions-30-6139299

Power BI DAX DATEVALUE function

The DAX DATEVALUE function returns the date with the system time. The syntax of this Power BI DAX DATEVALUE is:

DATEVALUE (Date)

Let me return the date of the hire date.

DtValue = DATEVALUE (EmployeeSales [HireDate])
power-bi-dax-date-functions-31-2184160

Power BI DAX TIMEVALUE role

The Power BI DAX TIMEVALUE function returns the time with the system default date. The syntax for the Power BI DAX TIMEVALUE function is:

TIMEVALUE (Date)

Let me return the time value of the hire date. Remember, 12/30/1899 is the default Power BI generated to date.

TimeVal = TIMEVALUE (EmployeeSales [HireDate])
power-bi-dax-date-functions-32-7715917

CALENDAR function in Power BI DAX

The CALENDAR function in Power BI DAX returns a table of dates from the start date to the end date. The syntax for a Power BI DAX CALENDAR function is:

CALENDAR (Start_Date, End_Date)

The following Power BI DAX CALENDAR function returns dates from January 1, 2018 to January 15, 2018. I suggest you refer to the article Create a calculated table to understand the process of creating a new table.

Calender = CALENDAR (DATE (2018,1, 1), DATE (2018, 1, 15))
power-bi-dax-date-functions-15-5093909

Let me create a table using this output

power-bi-dax-date-functions-16-3043192

Power BI DAX CALENDARAUTO function

The DAX CALENDARAUTO function, which returns continuous dates using the date from the data model. The syntax of a Power BI DAX CALENDARAUTO is:

CALENDARAUTO (year_end_month)

The argument value is an option for this Power BI DAX CALENDARAUTO function. Accepts numbers from 1 to 12 months and, if omitted, treats the value as 12 (default)

CalenderAutotable = CALENDARAUTO ()
power-bi-dax-date-functions-20-2220113

You can see the result of this Power BI date function

power-bi-dax-date-functions-21-2937267

How to use Power BI DAX date functions with examples? Microsoft Power BI DAX provides various date functions such as year, month, day, calendar, date, time, dateiff, now, today, utcnow, utctoday, datevalue, timevalue, etc.

To demonstrate these Power BI DAX Date features, we used the data shown below. As you can see, there are 15 records in this table.

power-bi-dax-date-functions-1-7298587

Power BI DAX date functions

The following series of examples shows the list of DAX date functions in Power BI

Power BI DAX NOW feature

The Power BI DAX Now function is one of the Power BI date functions, used to return the current date and time. The syntax for this Power BI DAX NOW is

NOW ()

To demonstrate the functions of Power BI DAX NOW, we have to use the Calculated column. To create a column, click the New column option under the Home tab, or Modeling tongue.

power-bi-dax-date-functions-2-2668610

We rename the default column name as Today. As you can see in the screenshot below, while typing, Power BI displays the suggestions.

power-bi-dax-date-functions-3-9376601

Clicking Enter or any key creates a new column.

power-bi-dax-date-functions-4-3100626

Let me add this column to the table we created earlier. See the Create Table Report article to understand the steps required to create a table.

power-bi-dax-date-functions-5-8230769

As you can see from the screenshot above, it shows today's date and time.

Power BI DAX DAY function

The DAY function in Power BI DAX extracts or returns the day number of the specified date. The syntax for the Power BI DAX DAY function is

DAY (Date)

Let me create a column to return the day number from the Hire Date column

DayinDate = DAY (EmployeeSales [HireDate])
power-bi-dax-date-functions-6-3281288

Power BI DAX MES function

The Power BI DAX month function returns the number of the month as of a specified date. The syntax for the Power BI DAX MONTH function is

MONTH (Date)

Let me create a column to return the month number from the Hire Date column

MonthinDate = MONTH (EmployeeSales [HireDate])

Here, we add the result of a previous function (day function) to the Table.

power-bi-dax-date-functions-7-6639266

Power BI DAX YEAR function

The Power BI DAX YEAR function extracts or returns the year number from the specified date. The syntax of this Power BI DAX YEAR:

YEAR (Date)

Let me create a column to return the year from the hire date column

YearInDt = YEAR (EmployeeSales [HireDate])
power-bi-dax-date-functions-8-3528892

Power BI DAX HOUR function

The TIME function in Power BI DAX returns Hours (in 24-hour format) since a specified date. The syntax for the Power BI DAX HOUR function is:

HOUR (DateTime)

Let me create a column to return the hours from the hire date column

Hour = HOUR (EmployeeSales [HireDate])
power-bi-dax-date-functions-9-3665215

MINUTE function of Power BI DAX

The Power BI DAX MINUTE function returns the number of minutes. The syntax of this Power BI DAX MINUTE is:

MINUTE (DateTime)

Let me create a column to return the number of minutes from the Hire Date column

Min = MINUTE (EmployeeSales [HireDate])
power-bi-dax-date-functions-10-4700845

Power BI DAX SECOND function

The SECOND function in Power BI DAX returns the number of seconds. The syntax of this Power BI DAX SECOND is:

SECOND (DateTime)

Let me create a column to return the number of seconds in the Hire Date column

Sec = SECOND (EmployeeSales [HireDate])
power-bi-dax-date-functions-11-1583272

Let me add the results of the Year, Month, Day, Hour, Minute, and Second functions to the table.

power-bi-dax-date-functions-12-6935550

Power BI DAX TODAY function

The Power BI DAX Today function returns today's date with the default system time. The syntax for this Power BI DAX Today is:

TODAY ()

Let me create a column to display today's date.

Today 2 = TODAY ()
power-bi-dax-date-functions-13-7619269

Let me compare NOW () and TODAY ().

In the screenshot below, you can see that there is a time difference between the two.

power-bi-dax-date-functions-14-4383108

Power BI DAX DATE function

The Power BI DAX Date function is useful for returning the date of the year, month, and day. The syntax for the Power BI DAX Date function is:

DATE (year, month, day)

Let me create a single column to create a date from the year, month and day

Date = DATE (YEAR (EmployeeSales [HireDate]), MONTH (EmployeeSales [HireDate]), DAY (EmployeeSales [HireDate]))
power-bi-dax-date-functions-17-8708226

Power BI DAX TIME function

The time function in Power BI DAX returns the time from the specified hour, minute, and second. The syntax for this Power BI DAX time function is:

TIME (hour, minute, second)

Let me create a column to create the time from the hour, minutes, and seconds.

Time = TIME (HOUR (EmployeeSales [HireDate]), MINUTE (EmployeeSales [HireDate]), SECOND (EmployeeSales [HireDate]))
power-bi-dax-date-functions-18-7597125

Let me add the result of the Power BI DAX Time function to the table report. If you find the output as the default date with time, change the data type from date / time to time.

power-bi-dax-date-functions-19-7155079

Power BI DAX day of week feature

The Power BI DAX Weekday function returns numbers from 1 to 7, where 1 from Sunday and 7 from Monday. The Power BI DAX Weekday function syntax is: The Power BI DAX Weekday function syntax is:

WEEKDAY (Date, Number)
  • If Number = 1 then 1 from Sunday
  • If Number = 2 then 1 from Monday
  • and Number = 3 then 0 from Monday

Let me create a column to find the number of the day of the week.

WeekDay = WEEKDAY (EmployeeSales [HireDate], 1)
power-bi-dax-date-functions-22-2422382

You can see the numbers of the week

power-bi-dax-date-functions-23-5470218

Power BI DAX WeekNum function

The Power BI DAX WeekNum function returns the week number (1 as the first week of January). The syntax for this Power BI DAX Weeknum function is:

WEEKNUM (Date, Number)

If Number = 1 The week starts on Sunday and if Number = 2, the Week starts on Monday. Let me create a column to find the number of the week.

WeekNum = WEEKNUM (EmployeeSales [HireDate], 1)
power-bi-dax-date-functions-24-1411062

Power BI DAX UTCNOW function

The DAX UTCNOW function is the same as the Power BI DAX NOW function. However, it returns the date and time along with UTC. The syntax for this Power BI DAX UTCNOW function is:

UTCNOW ()

Let me create a column to find the date and time along with UTC

UTCNow = UTCNOW ()
power-bi-dax-date-functions-25-6086461

Power BI DAX UTCTODAY function

The Power BI DAX UTCTODAY function is the same as the ODAY function. However, it returns the date with the default time along with UTC. The syntax of this Power BI DAX UTCTODAY is:

UTCTODAY ()

Let me create a column to find the date with UTC

UTCToday = UTCTODAY ()
power-bi-dax-date-functions-26-2813771

Power BI DAX YEARFRAC function

The Power BI DAX YEARFRAC function returns the fractional difference between the start date and the end date. The Power BI DAX YEARFRAC syntax is:

YEARFRAC (Start_Date, End_Date)

Let me find the exact number of years difference between the hire date and today

YearFrac = YEARFRAC (EmployeeSales [HireDate], NOW ())
power-bi-dax-date-functions-27-8972791

Power BI DAX EDATE function

The Power BI DAX EDATE function returns earlier dates or future dates. For example, the expiration date of the policy adding 24 as the second argument, etc. The syntax for this Power BI DAX EDATE function is:

EDATE (Start_Date, Months)

Let me add 2 months to the hire date

Edate = EDATE (EmployeeSales [HireDate], 2)
power-bi-dax-date-functions-28-5945695

Power BI DAX EOMONTH function

The Power BI DAX EOMONTH function (EOMONTH means the end of the month) displays the last day of a month. You can use the second argument to return earlier or future dates. The syntax of this Power BI DAX EDATE is:

EOMONTH (Start_Date, Months)

Let me add 3 months to the hire date. The following statement first adds three months to the hire date and then returns the last day of that month.

Eomonth = EOMONTH (EmployeeSales [HireDate], 3)
power-bi-dax-date-functions-29-4609994

Power BI DAX DATEDIFF function

The Power BI DAX DATEDIFF function returns the date difference between the start date and the end date. You can use the third argument of the Power BI DAX DATEDIFF function to select the difference value.

The Power BI DAX DATEDIFF function accepts YEAR, MONTH, DAY, SECOND, MINUTE, HOUR, QUARTER, WEEK. For example, if you select MONTH, the DateDiff function returns the number of the month difference between a start date and an end date.

The syntax for this Power BI DAX DATEDIFF function is:

DATEDIFF (Start_Date, End_Date, Difference_Value)

Let me check the difference between the hire date and today in years.

DateDiff = DATEDIFF (EmployeeSales [HireDate], NOW (), YEAR)
power-bi-dax-date-functions-30-8828739

Power BI DAX DATEVALUE function

The DAX DATEVALUE function returns the date with the system time. The syntax of this Power BI DAX DATEVALUE is:

DATEVALUE (Date)

Let me return the date of the hire date.

DtValue = DATEVALUE (EmployeeSales [HireDate])
power-bi-dax-date-functions-31-9173435

Power BI DAX TIMEVALUE role

The Power BI DAX TIMEVALUE function returns the time with the system default date. The syntax for the Power BI DAX TIMEVALUE function is:

TIMEVALUE (Date)

Let me return the time value of the hire date. Remember, 12/30/1899 is the default Power BI generated to date.

TimeVal = TIMEVALUE (EmployeeSales [HireDate])
power-bi-dax-date-functions-32-7715917

CALENDAR function in Power BI DAX

The CALENDAR function in Power BI DAX returns a table of dates from the start date to the end date. The syntax for a Power BI DAX CALENDAR function is:

CALENDAR (Start_Date, End_Date)

The following Power BI DAX CALENDAR function returns dates from January 1, 2018 to January 15, 2018. I suggest you refer to the article Create a calculated table to understand the process of creating a new table.

Calender = CALENDAR (DATE (2018,1, 1), DATE (2018, 1, 15))
power-bi-dax-date-functions-15-5193812

Let me create a table using this output

power-bi-dax-date-functions-16-3769957

Power BI DAX CALENDARAUTO function

The DAX CALENDARAUTO function, which returns continuous dates using the date from the data model. The syntax of a Power BI DAX CALENDARAUTO is:

CALENDARAUTO (year_end_month)

The argument value is an option for this Power BI DAX CALENDARAUTO function. Accepts numbers from 1 to 12 months and, if omitted, treats the value as 12 (default)

CalenderAutotable = CALENDARAUTO ()
power-bi-dax-date-functions-20-2220113

You can see the result of this Power BI date function

power-bi-dax-date-functions-21-5765066

How to use Power BI DAX date functions with examples? Microsoft Power BI DAX provides various date functions such as year, month, day, calendar, date, time, dateiff, now, today, utcnow, utctoday, datevalue, timevalue, etc.

To demonstrate these Power BI DAX Date features, we used the data shown below. As you can see, there are 15 records in this table.

power-bi-dax-date-functions-1-7298587

Power BI DAX date functions

The following series of examples shows the list of DAX date functions in Power BI

Power BI DAX NOW feature

The Power BI DAX Now function is one of the Power BI date functions, used to return the current date and time. The syntax for this Power BI DAX NOW is

NOW ()

To demonstrate the functions of Power BI DAX NOW, we have to use the Calculated column. To create a column, click the New column option under the Home tab, or Modeling tongue.

power-bi-dax-date-functions-2-2668610

We rename the default column name as Today. As you can see in the screenshot below, while typing, Power BI displays the suggestions.

power-bi-dax-date-functions-3-9376601

Clicking Enter or any key creates a new column.

power-bi-dax-date-functions-4-3100626

Let me add this column to the table we created earlier. See the Create Table Report article to understand the steps required to create a table.

power-bi-dax-date-functions-5-8230769

As you can see from the screenshot above, it shows today's date and time.

Power BI DAX DAY function

The DAY function in Power BI DAX extracts or returns the day number of the specified date. The syntax for the Power BI DAX DAY function is

DAY (Date)

Let me create a column to return the day number from the Hire Date column

DayinDate = DAY (EmployeeSales [HireDate])
power-bi-dax-date-functions-6-3281288

Power BI DAX MES function

The Power BI DAX month function returns the number of the month as of a specified date. The syntax for the Power BI DAX MONTH function is

MONTH (Date)

Let me create a column to return the month number from the Hire Date column

MonthinDate = MONTH (EmployeeSales [HireDate])

Here, we add the result of a previous function (day function) to the Table.

power-bi-dax-date-functions-7-6639266

Power BI DAX YEAR function

The Power BI DAX YEAR function extracts or returns the year number from the specified date. The syntax of this Power BI DAX YEAR:

YEAR (Date)

Let me create a column to return the year from the hire date column

YearInDt = YEAR (EmployeeSales [HireDate])
power-bi-dax-date-functions-8-3528892

Power BI DAX HOUR function

The TIME function in Power BI DAX returns Hours (in 24-hour format) since a specified date. The syntax for the Power BI DAX HOUR function is:

HOUR (DateTime)

Let me create a column to return the hours from the hire date column

Hour = HOUR (EmployeeSales [HireDate])
power-bi-dax-date-functions-9-9970130

MINUTE function of Power BI DAX

The Power BI DAX MINUTE function returns the number of minutes. The syntax of this Power BI DAX MINUTE is:

MINUTE (DateTime)

Let me create a column to return the number of minutes from the Hire Date column

Min = MINUTE (EmployeeSales [HireDate])
power-bi-dax-date-functions-10-9656612

Power BI DAX SECOND function

The SECOND function in Power BI DAX returns the number of seconds. The syntax of this Power BI DAX SECOND is:

SECOND (DateTime)

Let me create a column to return the number of seconds in the Hire Date column

Sec = SECOND (EmployeeSales [HireDate])
power-bi-dax-date-functions-11-3542883

Let me add the results of the Year, Month, Day, Hour, Minute, and Second functions to the table.

power-bi-dax-date-functions-12-6188746

Power BI DAX TODAY function

The Power BI DAX Today function returns today's date with the default system time. The syntax for this Power BI DAX Today is:

TODAY ()

Let me create a column to display today's date.

Today 2 = TODAY ()
power-bi-dax-date-functions-13-8637558

Let me compare NOW () and TODAY ().

In the screenshot below, you can see that there is a time difference between the two.

power-bi-dax-date-functions-14-7890296

Power BI DAX DATE function

The Power BI DAX Date function is useful for returning the date of the year, month, and day. The syntax for the Power BI DAX Date function is:

DATE (year, month, day)

Let me create a single column to create a date from the year, month and day

Date = DATE (YEAR (EmployeeSales [HireDate]), MONTH (EmployeeSales [HireDate]), DAY (EmployeeSales [HireDate]))
power-bi-dax-date-functions-17-5974344

Power BI DAX TIME function

The time function in Power BI DAX returns the time from the specified hour, minute, and second. The syntax for this Power BI DAX time function is:

TIME (hour, minute, second)

Let me create a column to create the time from the hour, minutes, and seconds.

Time = TIME (HOUR (EmployeeSales [HireDate]), MINUTE (EmployeeSales [HireDate]), SECOND (EmployeeSales [HireDate]))
power-bi-dax-date-functions-18-3956624

Let me add the result of the Power BI DAX Time function to the table report. If you find the output as the default date with time, change the data type from date / time to time.

power-bi-dax-date-functions-19-3621115

Power BI DAX day of week feature

The Power BI DAX Weekday function returns numbers from 1 to 7, where 1 from Sunday and 7 from Monday. The Power BI DAX Weekday function syntax is: The Power BI DAX Weekday function syntax is:

WEEKDAY (Date, Number)
  • If Number = 1 then 1 from Sunday
  • If Number = 2 then 1 from Monday
  • and Number = 3 then 0 from Monday

Let me create a column to find the number of the day of the week.

WeekDay = WEEKDAY (EmployeeSales [HireDate], 1)
power-bi-dax-date-functions-22-2422382

You can see the numbers of the week

power-bi-dax-date-functions-23-6153699

Power BI DAX WeekNum function

The Power BI DAX WeekNum function returns the week number (1 as the first week of January). The syntax for this Power BI DAX Weeknum function is:

WEEKNUM (Date, Number)

If Number = 1 The week starts on Sunday and if Number = 2, the Week starts on Monday. Let me create a column to find the number of the week.

WeekNum = WEEKNUM (EmployeeSales [HireDate], 1)
power-bi-dax-date-functions-24-1310560

Power BI DAX UTCNOW function

The DAX UTCNOW function is the same as the Power BI DAX NOW function. However, it returns the date and time along with UTC. The syntax for this Power BI DAX UTCNOW function is:

UTCNOW ()

Let me create a column to find the date and time along with UTC

UTCNow = UTCNOW ()
power-bi-dax-date-functions-25-6086461

Power BI DAX UTCTODAY function

The Power BI DAX UTCTODAY function is the same as the ODAY function. However, it returns the date with the default time along with UTC. The syntax of this Power BI DAX UTCTODAY is:

UTCTODAY ()

Let me create a column to find the date with UTC

UTCToday = UTCTODAY ()
power-bi-dax-date-functions-26-1852318

Power BI DAX YEARFRAC function

The Power BI DAX YEARFRAC function returns the fractional difference between the start date and the end date. The Power BI DAX YEARFRAC syntax is:

YEARFRAC (Start_Date, End_Date)

Let me find the exact number of years difference between the hire date and today

YearFrac = YEARFRAC (EmployeeSales [HireDate], NOW ())
power-bi-dax-date-functions-27-4788488

Power BI DAX EDATE function

The Power BI DAX EDATE function returns earlier dates or future dates. For example, the expiration date of the policy adding 24 as the second argument, etc. The syntax for this Power BI DAX EDATE function is:

EDATE (Start_Date, Months)

Let me add 2 months to the hire date

Edate = EDATE (EmployeeSales [HireDate], 2)
power-bi-dax-date-functions-28-5147438

Power BI DAX EOMONTH function

The Power BI DAX EOMONTH function (EOMONTH means the end of the month) displays the last day of a month. You can use the second argument to return earlier or future dates. The syntax of this Power BI DAX EDATE is:

EOMONTH (Start_Date, Months)

Let me add 3 months to the hire date. The following statement first adds three months to the hire date and then returns the last day of that month.

Eomonth = EOMONTH (EmployeeSales [HireDate], 3)
power-bi-dax-date-functions-29-2466372

Power BI DAX DATEDIFF function

The Power BI DAX DATEDIFF function returns the date difference between the start date and the end date. You can use the third argument of the Power BI DAX DATEDIFF function to select the difference value.

The Power BI DAX DATEDIFF function accepts YEAR, MONTH, DAY, SECOND, MINUTE, HOUR, QUARTER, WEEK. For example, if you select MONTH, the DateDiff function returns the number of the month difference between a start date and an end date.

The syntax for this Power BI DAX DATEDIFF function is:

DATEDIFF (Start_Date, End_Date, Difference_Value)

Let me check the difference between the hire date and today in years.

DateDiff = DATEDIFF (EmployeeSales [HireDate], NOW (), YEAR)
power-bi-dax-date-functions-30-6139299

Power BI DAX DATEVALUE function

The DAX DATEVALUE function returns the date with the system time. The syntax of this Power BI DAX DATEVALUE is:

DATEVALUE (Date)

Let me return the date of the hire date.

DtValue = DATEVALUE (EmployeeSales [HireDate])
power-bi-dax-date-functions-31-2184160

Power BI DAX TIMEVALUE role

The Power BI DAX TIMEVALUE function returns the time with the system default date. The syntax for the Power BI DAX TIMEVALUE function is:

TIMEVALUE (Date)

Let me return the time value of the hire date. Remember, 12/30/1899 is the default Power BI generated to date.

TimeVal = TIMEVALUE (EmployeeSales [HireDate])
power-bi-dax-date-functions-32-7715917

CALENDAR function in Power BI DAX

The CALENDAR function in Power BI DAX returns a table of dates from the start date to the end date. The syntax for a Power BI DAX CALENDAR function is:

CALENDAR (Start_Date, End_Date)

The following Power BI DAX CALENDAR function returns dates from January 1, 2018 to January 15, 2018. I suggest you refer to the article Create a calculated table to understand the process of creating a new table.

Calender = CALENDAR (DATE (2018,1, 1), DATE (2018, 1, 15))
power-bi-dax-date-functions-15-5193812

Let me create a table using this output

power-bi-dax-date-functions-16-3043192

Power BI DAX CALENDARAUTO function

The DAX CALENDARAUTO function, which returns continuous dates using the date from the data model. The syntax of a Power BI DAX CALENDARAUTO is:

CALENDARAUTO (year_end_month)

The argument value is an option for this Power BI DAX CALENDARAUTO function. Accepts numbers from 1 to 12 months and, if omitted, treats the value as 12 (default)

CalenderAutotable = CALENDARAUTO ()
power-bi-dax-date-functions-20-4349390

You can see the result of this Power BI date function

power-bi-dax-date-functions-21-1615661
error: Attention: Protected content.