Microsoft Power BI DAX provides various Date Functions such as: Year, Month, Day, Calendar, date, time, datediff, now, today, utcnow, utctoday, datevalue, timevalue, etc. In this article we will how you, How to use Power BI DAX Date Functions with examples.
To demonstrate these Power BI DAX Date functions, we are going to use the below shown data. As you can see, there are 15 records in this table.
Power BI DAX Date Functions
The following series of examples will show you the list of DAX Date Functions in Power BI
Power BI DAX NOW Function
The DAX Now function is used to return the current date and time. Syntax of this Power BI DAX NOW is as shown below:
NOW()
To demonstrate these Date functions, we have to use Calculated column. In order to create a column, please click on the New Column option under the Home tab, or Modeling tab.
We renamed the default column name as Today. As you can see from the below screenshot, while I was typing, Power BI is showing the suggestions.
By clicking the enter or any key, a new column will be created.
Let me add this column to the table that we created earlier. Please refer Create Table Report article to understand the steps involved in creating a table
You can see from the above screenshot, it is display the today’s date and time
Power BI DAX DAY Function
The DAX DAY function is used to extract or return Day number from the given date. The syntax of this Power BI DAX DAY Function is as shown below:
DAY(Date)
Let me create a column to return Day number from Hire date column
DayinDate = DAY(EmployeeSales[HireDate])
Power BI DAX MONTH Function
The DAX Month function is used to return Month number from a given date. The syntax of this Power BI DAX MONTH Function is
MONTH(Date)
Let me create a column to return Month number from Hire date column
MonthinDate = MONTH(EmployeeSales[HireDate])
Here, we added the result of a previous function (day Function) to the Table.
Power BI DAX YEAR Function
The DAX YEAR function is used to extract or return year number from the given date. Syntax of this Power BI DAX YEAR:
YEAR(Date)
Let me create a column to return Year from Hire date column
YearInDt = YEAR(EmployeeSales[HireDate])
Power BI DAX HOUR Function
The DAX HOUR function in Power BI is used to return Hours (in 24 hours format) from a given date. Syntax of this Power BI DAX HOUR Function is:
HOUR(DateTime)
Let me create a column to return Hours from Hire date column
Hour = HOUR(EmployeeSales[HireDate])
Power BI DAX MINUTE Function
The DAX MINUTE function is used to return number of minutes from a given date. Syntax of this Power BI DAX MINUTE is:
MINUTE(DateTime)
Let me create a column to return number of minutes from Hire date column
Min = MINUTE(EmployeeSales[HireDate])
Power BI DAX SECOND Function
The DAX SECOND function is used to return number of seconds from a given date. Syntax of this Power BI DAX SECOND is:
SECOND(DateTime)
Let me create a column to return number of seconds from Hire date column
Sec = SECOND(EmployeeSales[HireDate])
Let me add the results of Year, Month, Day, Hour, Minute, and Second function results to the table.
Power BI DAX TODAY Function
The DAX Today function is used to return today’s date with default system time. Syntax of this Power BI DAX Today is:
TODAY()
Let me create a column to display today’s date
Today 2 = TODAY()
Let me compare both NOW(), and TODAY().
From the below screenshot, you can see there is a time difference between two of them.
Power BI DAX DATE Function
The DAX Date function is used to return date from year, month, and day. Syntax of this Power BI DAX Date is:
DATE(year, month, day)
Let me create a column to create a date from year, month, and day
Date = DATE(YEAR(EmployeeSales[HireDate]), MONTH(EmployeeSales[HireDate]),
DAY(EmployeeSales[HireDate]))
Power BI DAX TIME Function
The DAX Time function is used to return time from specified hour, minute, and seconds. Syntax of this Power BI DAX Time is:
TIME(hour, minute, second)
Let me create a column to create a time from hour, minute, and seconds
Time = TIME(HOUR(EmployeeSales[HireDate]), MINUTE(EmployeeSales[HireDate]),
SECOND(EmployeeSales[HireDate]))
Let me add Time function result to the table report. If you find the output as the default date with the time then, change the data type from date/Time to Time.
Power BI DAX Weekday Function
The DAX Weekday function will return numbers from 1 to 7, where 1 as Sunday, and 7 as Monday. Syntax of this Power BI DAX Weekday is:
WEEKDAY(Date, Number)
- If Number = 1 then 1 as Sunday
- If Number = 2 then 1 as Monday
- and Number = 3 then 0 as Monday
Let me create a column to find the weekday number
WeekDay = WEEKDAY(EmployeeSales[HireDate], 1)
You can see the Week numbers
Power BI DAX WeekNum Function
The DAX WeekNum function will return week number ( 1 as January first week). Syntax of this Power BI DAX Weeknum is:
WEEKNUM(Date, Number)
If Number = 1 Week begins on Sunday, and If Number = 2 then Week begins on Monday. Let me create a column to find the week number
WeekNum = WEEKNUM(EmployeeSales[HireDate], 1)
Power BI DAX UTCNOW Function
The DAX UTCNOW function is same as NOW() function however, it will return date and time along with UTC. Syntax of this Power BI DAX UTCNOW is:
UTCNOW()
Let me create a column to find the date and time along with UTC
UTCNow = UTCNOW()
Power BI DAX UTCTODAY Function
The DAX UTCTODAY function is same as ODAY() function however, it will return date with default time along with UTC. 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 DAX YEARFRAC function will return the fractional difference between start date and end date. Syntax of this Power BI DAX YEARFRAC is:
YEARFRAC(Start_Date, End_Date)
Let me find the exact number of years difference in-between Hire Date and Today
YearFrac = YEARFRAC(EmployeeSales[HireDate], NOW())
Power BI DAX EDATE Function
The DAX EDATE function is used to return previous dates, or future dates. For example, policy mature date by adding 24 as the second argument etc. Syntax of this Power BI DAX EDATE is:
EDATE(Start_Date, Months)
Let me add 2 months to Hire Date
Edate = EDATE(EmployeeSales[HireDate], 2)
Power BI DAX EOMONTH Function
The DAX EOMONTH function (EOMONTH means end of month) is used to display the last day in a month. You can use second argument to return previous dates, or future dates. Syntax of this Power BI DAX EDATE is:
EOMONTH(Start_Date, Months)
Let me add 3 months to Hire Date. below statement will first add three months to Hire date,and then returns the last day of that month.
Eomonth = EOMONTH(EmployeeSales[HireDate], 3)
Power BI DAX DATEDIFF Function
The DAX DATEDIFF function is used to return the date difference between start date and end date. You can use third argument to select the difference value.
It accepts YEAR, MONTH, DAY, SECOND, MINUTE, HOUR, QUARTER, WEEK. For example, if you select MONTH then DateDiff function will return the number of month difference between start date and end date.
Syntax of this Power BI DAX DATEDIFF is:
DATEDIFF(Start_Date, End_Date, Difference_Value)
Let me check the difference between Hire date and Today in years.
DateDiff = DATEDIFF(EmployeeSales[HireDate], NOW(), YEAR)
Power BI DAX DATEVALUE Function
The DAX DATEVALUE function is used to return the date with system time. Syntax of this Power BI DAX DATEVALUE is:
DATEVALUE(Date)
Let me return the Date from Hire date.
DtValue = DATEVALUE(EmployeeSales[HireDate])
Power BI DAX TIMEVALUE Function
The DAX TIMEVALUE function is used to return the time with system default date. Syntax of this Power BI DAX TIMEVALUE is:
TIMEVALUE(Date)
Let me return the Time value from Hire date. Remember, 12/30/1899 is the default Power BI generated date.
TimeVal = TIMEVALUE(EmployeeSales[HireDate])
Power BI DAX CALENDAR Function
The DAX CALENDAR function is used to return a table of dates from start date to end date. Syntax of this Power BI DAX CALENDAR is:
CALENDAR(Start_Date, End_Date)
Below statement will return dates from Jan 1, 2018 to January 15, 2018. I suggest you to refer Create a calculated table article to understand the process of creating 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 will return continues dates using the data model date. Syntax of this Power BI DAX CALENDARAUTO is:
CALENDARAUTO(year_end_month)
The argument value is option for this function. If accepts numbers from 1 to 12 months, and if you omit, it will treat the value as 12 (default)
CalenderAutotable = CALENDARAUTO()
You can see the output
