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
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.
We rename the default column name as Today. As you can see in the screenshot below, while typing, Power BI displays the suggestions.
Clicking Enter or any key creates a new column.
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.
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 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 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 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])
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 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])
Let me add the results of the Year, Month, Day, Hour, Minute, and Second functions to the table.
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 ()
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 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 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]))
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 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)
You can see the numbers of the week
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 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 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 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 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 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 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 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 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])
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))
Let me create a table using this output
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 ()
You can see the result of this Power BI date function
