DAX: Date and Time functions June 26th, 2013

Vinod Kumar

We have been looking at various DAX related content in this blog in the past few weeks, I thought let me start off by bringing out some of the commonly used DAX functions to light. Many are most useful and seem to work just like normal Excel functions. In this blog we will look at Date and time functions available with DAX.

The Date and Time functions convert string or number to a datetime value or convert a datetime value to a number representing the different part of the date time, such as year, month, day of a week, hour, minute.

The datetime argument can be an explicit datetime value, a column of datetime data type or an expression that evaluates to a datetime value. The result depends on the locale and date time setting which affects whether the datetime value is interpreted as mm-dd-yyyy, or as dd-mm-yyyy.

The value of the datetime argument can be in any of the proper datetime formats. Here are some example datetime formats:

  • "August 1, 2012"
  • "Sep 12, 2012"
  • "3-4-2012"
  • "2012-03-04"
  • "03/04/2012"

DATE Function

Syntax: DATE (yyyy, mm, dd)

Return type: DateTime

DATE function generates a date value from the separately provided value of year, month, and date. The year, month, and date arguments are rounded to integer if non- integer values are presented. The arguments can have explicit values, or expression evaluates to the value. The resulting date is generated using the following logic:

If yyyy is less than 1900,
     Resulting year = 1900 + yyyy
Else
     Resulting year = yyyy
If mm is between 1 to 12,
     Resulting month = mm
Else
     Resulting month = beginning of the resulting year + mm
If dd is between 1 and end of the resulting month
     Resulting date = dd
Else
     Resulting date = beginning of the date of the resulting year + dd

Examples:

=DATE(08,13,32)

The above example returns February 1, 1909.

=DATE(2010,1,-7)

The above example returns December 24, 2009

DATEVALUE Function

Syntax: DATEVALUE (date_text)

Return type: DateTime

DATEVALUE function converts date value represented in a text string to date value in DateTime type. The resulting date depending on the locale setting which controls how to interpret certain date format

Example:

=DATEVALUE ("8/1/2012")

The result in US will be August 1, 2012. The result in India will be January 8, 2012.

TIME Function

TIMEVALUE Function

Similar to DATE and DATEVALUE functions, TIME function converts the provided numbers for hour, minute, and second to a time value, TIMEVALUE converts a string time value to a time value of datetime type.

Syntax: TIME (hour, minute, second) and TIMEVALUE (time_text)

Return data type: datetime

DAY Function

MONTH Function

YEAR Function

WEEKDAY Function

WEEKNUM Function

Syntax: DAY(datetime)

MONTH (datetime)

YEAR (datetime)

WEEKDAY (datetime, <return_type>)

WEEKNUM (datetime, <return_type>)

Return data type: integer

These functions return the day number of the month (1-31), the month number of the year (1-12), the year, the day number within the week (0-6 or 1-7), or the week number within the year (1-53) from the datetime value passed to the argument. The return_type argument for the WEEKDAY function specifies when a week starts and whether the first day needs to return 0 or 1. If not specified, 1 is the default value.

  • 1 Week begins on Sunday (1) and ends on Saturday (7)
  • 2 Week begins on Monday (1) and ends on Sunday (7)
  • 3 Week begins on Monday (0) and ends on Sunday (6)

The return type for WEEKNUM function specifies whether a week starts on Sunday or Monday.

  • 1 Week begins on Sunday. Weekdays are numbered 1 through 7
  • 2 Week begins on Monday. Weekdays are numbered 1 through 7

EDATE Function

Syntax: EDATE (start_date, numberofMonths)

Return data type: datetime

EDATE returns the datetime before or after the number of months from the start_date. Useful for calculating the maturity date or due date that needs to be on the same day as the start_date. When the resulting day goes beyond the end of the month, the end of the month is returned.

Example:

=EDATE("3-4-2013", 3)

It returns June 4, 2013.

=EDATE("March 30, 2013", -1)

It returns February 28, 2013.

EOMONTH Function

Syntax: EOMONTH ( start_date, numberOfMonths)

Return data type: datetime

EOMONTH returns the datetime of the end of the month before or after the specified number of months from the start_date. Useful for calculating due date that needs to be the end of the month.

Example:

=EOMONTH("3/4/2013", 1)

It returns April 30, 2013.

HOUR Function

MINUTE Function

SECOND Function

Syntax: HOUR (datetime)

MINUTE (datetime)

SECOND (datetime)

Return data type: Integer

These functions return the number of hours (0-23), minute (0-59), and second (0-59) from the input datetime value.

NOW Function

Syntax: NOW()

Return data type: datetime

NOW returns the current date and time. Only updated when we refresh PowerPivot data. This is similar to Excel function.

TODAY Function

Syntax: TODAY ()

Return data type: datetime

TODAY returns the current date. Similar to NOW function, once the value is populated, it will not change until you refresh the PowerPivot workbook. This functions similar to Excel function.

YEARFRAC Function

Syntax: YEARFRAC(start_date, end_date, <basis>)

Return data type: decimal

YEARFRAC returns a fraction of the days between the start_date and the end_date to the total number of days in a year. <Basis> arguments defines the type of day count

Basis Description

  • 0 US (NASD) 30/360
  • 1 Actual/actual
  • 2 Actual/360
  • 3 Actual/365
  • 4 European 30/360

Hope you learnt the typical use of various functions with DAX. In future posts, we will take a look at other functions and how they work with DAX. Do let me know if you want me to take any other functions next.

Tags: , , , , , , , ,

This entry was posted on Wednesday, June 26th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply