Date And Time Functions (DAX) In Power BI - Part Two

Introduction

 
In this article I will introduce the rest of the ‘Date and Time’ DAX functions that I didn't discuss in the previous article. I  explained 11 ‘Date and Time’ functions in the previous article, the rest are as follows:
 

UTCTODAY

 
Returns the current date in datetime format expressed in coordinated universal time (UTC).
 
Syntax- UTCTODAY()
 
Return Value- Date
 
Date And Time Functions (DAX) In Power BI 
 

UTCNOW

 
Returns a current date and time in datetime format expressed in coordinated universal time (UTC).
 
Syntax- UTCNOW()
 
Return Value- Datetime
 
Date And Time Functions (DAX) In Power BI 
 

DATE

 
Returns a specified Date in datetime
 
Syntax- DATE(Year,Month,Day)
 
Return value - Date in datetime
 
Year
 
A number represents the year. It can be a 1 to 4 digit number. If the number you entered is 0-1899 then it will be added to 1900. A year with a value of greater than 9999 or less than 0 will not allowed.
 
Month
 
A number represents the month. This number will be 1 -12. If you enter greater than 12, then it will evaluate it as a month of next year. Ex –(1999,14,1) will be 1 Feb 2000.
 
Day
 
A number represent a Day. 1 – 31 are valid numbers. Numbers greater than 31 will be treated as a day of the next month. Ex- (1999, 5, 32) will be 1 June 1999.
 
Ex
 
Date And Time Functions (DAX) In Power BI
 
Date And Time Functions (DAX) In Power BI 
 

DATEDIFF

 
Returns the number of units (unit specified in interval) between the input two dates.
 
Syntax- DATEDIFF(Date1, Date2, Interval)
 
Date1- A scalar datetime value
 
Date2- A scalar datetime
 
Interval- This interval can be ‘SECOND’, ‘MINUTE’, ‘HOUR’, ‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’, ‘YEAR’
 
Date And Time Functions (DAX) In Power BI 
 

DATEVALUE

 
Converts a date in the form of text to a date in datetime format.
 
Syntax- DATEVALUE(DateText)
 
DateText- Text that represents a date.
 
Ex
 
DATEVALUE(“8/24/2020”) return 8/24/2020 12:00:00 am.
 
Date And Time Functions (DAX) In Power BI 
 

EDATE

 
Returns the date that is indicated number of month before or after the start date.
 
Syntax-EDATE(StartDate, Months)
 
StartDate- A date in datetime format or text format.
 
Ex- EDATE(“1-12-2020”, 4) will return 5/12/2020
 
Date And Time Functions (DAX) In Power BI 
 

Time

 
Converts hours, minutes, and seconds given as numbers to a time in datetime format.
 
Syntax- TIME(Hour, Minute, Second)
 
Hour
 
A number from 0 to 23. Numbers greater than 23 will be divided by 24 and the reminder will be treated as hour.
 
Minute
 
A number from 0 to 59. If a number is greater than 59 then it will be converted in hours or minutes accordingly.
 
Seconds
 
A number from 0 to 59 and above  59 will be converted in minutes and seconds accordingly
 
Ex
 
Date And Time Functions (DAX) In Power BI
 
Date And Time Functions (DAX) In Power BI
 
Date And Time Functions (DAX) In Power BI 
 

TIMEVALUE

 
Converts a time in text format to a time in datetime format.
 
Syntax- TIMEVALUE(TimeText)
 
TimeText
 
A text that represents a specific time of the day.
 
Ex- TIMEVALUE(“12:23:42”)
 
Date And Time Functions (DAX) In Power BI 
 

NOW

 
Returns a current date and time in datetime format.
 
Syntax- NOW()
 
Ex
 
Date And Time Functions (DAX) In Power BI 
 
Note
The difference between Today() and Now() is the return time. Today() returns the current date but default time as 12:00:00 whereas Now() returns the current date and current time
 

HOUR

 
Returns the hour as a number from 0 (12:00 am) to 23 (11:00 pm)
 
Syntax- HOUR(Datetime)
 
Datetime
 
A datetime or text value such as 16:48:00 or 4:48 pm.
 
Ex- HOUR(“3:00 pm”) will return 15
 
Date And Time Functions (DAX) In Power BI 
 

MINUTE

 
Returns a number from 0 to 59 representing the minute.
 
Syntax- MINUTE(Datetime)
 
Datetime
 
A datetime or text value in specific time format such as 5: 20 pm or 17:20:00.
 
Ex
 
Date And Time Functions (DAX) In Power BI 

SECOND

 
Returns a number from 0 to 59 representing the second.
 
Syntax- SECOND(Datetime)
 
Datetime
 
A datetime or text value in specific time format such as 1:28:45 pm or 13:28:45.
 
Ex
 
Date And Time Functions (DAX) In Power BI 
 

Summary

 
I have covered almost all ‘Date and Time’ DAX functions of Power BI in this article and previous articles that I mentioned in the introduction. I hope you understand these functions. I will continue to write the rest of the categories of DAX functions. So, stay with me. Thanks for reading.