CALENDAR
Returns a table with a single column named “Date” that contains a contiguous set of
dates. The range of dates is from the specified start date to the specified end date,
inclusive of those two dates.
Syntax
DAX Copy
CALENDAR(<start_date>, <end_date>)
Parameters
Term Definition
start_date Any DAX expression that returns a datetime value.
end_date Any DAX expression that returns a datetime value.
Return value
Returns a table with a single column named “Date” containing a contiguous set of dates.
The range of dates is from the specified start date to the specified end date, inclusive of
those two dates.
Example:
=CALENDAR (DATE (20015, 1, 1), DATE (2025, 12, 31))
=CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date]))
CALENDARAUTO
Returns a table with a single column named “Date” that contains a contiguous set of
dates. The range of dates is calculated automatically based on data in the model.
Syntax
DAX Copy
CALENDARAUTO([fiscal_year_end_month])
Parameters
Term Definition
Any DAX expression that returns an integer from 1 to 12. If omitted, defaults to
fiscal_year_end_month the value specified in the calendar table template for the current user,
if present; otherwise, defaults to 12.
Return value
Returns a table with a single column named “Date” that contains a contiguous set of
dates. The range of dates is calculated automatically based on data in the model.
Example:
In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June
30, 2011.
CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2011.
CALENDARAUTO(3) will return all dates between March 1, 2010 and February 28, 2012.
DATE
Returns the specified date in datetime format.
Syntax
DAX Copy
DATE(<year>, <month>, <day>)
Return value
Returns the specified date (datetime).
Example:
=DATE(2009,7,8)
=DATE(08,1,2)
If day is greater than the number of days in the month specified, day adds that number
of days to the first day in the month. The following formula returns the date February 4,
2008:
Code
DAX Copy
=DATE(2008,1,35)
Comment
If day is less than 1, day subtracts the magnitude that number of days, plus one, from
the first day of the month specified. The following formula returns December 16, 2007:
DAX Copy
=DATE(2008,1,-15)
DATEDIFF
Returns the count of interval boundaries crossed between two dates.
Syntax
DAX Copy
DATEDIFF(<start_date>, <end_date>, <interval>)
Parameters
Term Definition
start_da
A scalar datetime value.
te
end_dat
A scalar datetime value Return value.
e
The interval to use when comparing dates. The value can be one of
the following:
- SECOND
- MINUTE
interval - HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
Return value
The count of interval boundaries crossed between two dates.
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), MONTH )
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), QUARTER )
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), YEAR )
DATEVALUE
Converts a date in the form of text to a date in datetime format.
Syntax
DAX Copy
DATEVALUE(date_text)
Parameters
Term Definition
date_te Text that represents a
xt date.
Property Value/Return value
A date in datetime format.
Example
=DATEVALUE("8/1/2009")
DAY
Returns the day of the month, a number from 1 to 31.
Syntax
DAX Copy
DAY(<date>)
Parameters
Ter
Definition
m
A date in datetime format, or a text representation
date
of a date.
Return value
An integer number indicating the day of the month.
Example:
=DAY([Birthdate])
EDATE
Returns the date that is the indicated number of months before or after the start date.
Use EDATE to calculate maturity dates or due dates that fall on the same day of the
month as the date of issue.
Syntax
DAX Copy
EDATE(<start_date>, <months>)
Parameters
Term Definition
start_da
A date in datetime or text format that represents the start date.
te
An integer that represents the number of months before or after
months
start_date.
Return value
A date (datetime).
Example:
=EDATE([TransactionDate],3)
EOMONTH
Returns the date in datetime format of the last day of the month, before or after a
specified number of months. Use EOMONTH to calculate maturity dates or due dates that
fall on the last day of the month.
Syntax
DAX Copy
EOMONTH(<start_date>, <months>)
Parameters
Term Definition
start_da
The start date in datetime format, or in an accepted text representation of a date.
te
A number representing the number of months before or after the start_date. Note: If
months you enter a number that is not an integer, the number is rounded up or down to the
nearest integer.
Return value
A date (datetime).
Example:
=EOMONTH("March 3, 2008",1.5)
HOUR
Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
Syntax
DAX Copy
HOUR(<datetime>)
Parameters
Term Definition
datetim A datetime value, such as 16:48:00 or
e 4:48 PM.
Return value
An integer number from 0 to 23.
Example:
=HOUR("March 3, 2008 3:00 PM")
MINUTE
Returns the minute as a number from 0 to 59, given a date and time value.
Syntax
DAX Copy
MINUTE(<datetime>)
Parameters
Term Definition
datetim A datetime value or text in an accepted time format, such as
e 16:48:00 or 4:48 PM.
Return value
An integer number from 0 to 59.
Example:
=MINUTE("March 23, 2008 1:45 PM")
MONTH
Returns the month as a number from 1 (January) to 12 (December).
Syntax
DAX Copy
MONTH(<datetime>)
Parameters
Ter
Definition
m
A date in datetime or text
date
format.
Return value
An integer number from 1 to 12.
Example:
=MONTH("March 3, 2008 3:45 PM")
=MONTH(Orders[TransactionDate])
NOW
Returns the current date and time in datetime format.
The NOW function is useful when you need to display the current date and time on a
worksheet or calculate a value based on the current date and time, and have that value
updated each time you open the worksheet.
Syntax
DAX Copy
NOW()
Return value
A date (datetime).
Example:
=NOW()+3.5
SECOND
Returns the seconds of a time value, as a number from 0 to 59.
Syntax
DAX Copy
SECOND(<time>)
Parameters
Ter
Definition
m
A time in datetime format, such as 16:48:23 or
time
4:48:47 PM.
Return value
An integer number from 0 to 59.
Example:
=SECOND('Orders'[TransactionTime])
TIME
Converts hours, minutes, and seconds given as numbers to a time in datetime format.
Syntax
DAX Copy
TIME(hour, minute, second)
Parameters
Term Definition
A number from 0 to 23 representing the hour.
hour
Any value greater than 23 will be divided by 24 and the remainder will be treated as
the hour value.
A number from 0 to 59 representing the minute.
minut
e
Any value greater than 59 will be converted to hours and minutes.
A number from 0 to 59 representing the second.
secon
d
Any value greater than 59 will be converted to hours, minutes, and seconds.
Return value
A time (datetime).
Example:
=TIME(27,0,0)
=TIME(3,0,0)
TIMEVALUE
Converts a time in text format to a time in datetime format.
Syntax
DAX Copy
TIMEVALUE(time_text)
Parameters
Term Definition
time_te A text string that that represents a certain time of the day. Any date information
xt included in the time_text argument is ignored.
Return value
A date (datetime).
Example:
=TIMEVALUE("20:45:30")
TODAY
Returns the current date.
Syntax
DAX Copy
TODAY()
Return value
A date (datetime).
Example:
=YEAR(TODAY())-1963
WEEKDAY
Returns a number from 1 to 7 identifying the day of the week of a date. By default the
day ranges from 1 (Sunday) to 7 (Saturday).
Syntax
DAX Copy
WEEKDAY(<date>, <return_type>)
Parameters
Term Definition
A date in datetime format.
date
Dates should be entered by using the DATE function, by using expressions that result
in a date, or as the result of other formulas.
A number that determines the Return value:
Return type: 1, week begins on Sunday (1) and ends on Saturday (7). numbered 1
through 7.
return_ty
pe
Return type: 2, week begins on Monday (1) and ends on Sunday (7).
Return type: 3, week begins on Monday (0) and ends on Sunday (6).numbered 1
through 7.
Return value
An integer number from 1 to 7.
Example:
=WEEKDAY([HireDate]+1)
WEEKNUM
Returns the week number for the given date and year according to the return_type
value. The week number indicates where the week falls numerically within a year.
Syntax
DAX Copy
WEEKNUM(<date>, <return_type>)
Parameters
Term Definition
date The date in datetime format.
A number that determines the Return value: use 1 when the week begins on Sunday;
use 2 when the week begins on Monday. The default is 1.
return_ty
pe Return type: 1, week begins on Sunday. Weekdays are numbered 1 through 7.
Return type: 2, week begins on Monday. Weekdays are numbered 1 through 7.
Return value
An integer number.
Exmaple:
=WEEKNUM('Employees'[HireDate])
YEAR
Returns the year of a date as a four digit integer in the range 1900-9999.
Syntax
DAX Copy
YEAR(<date>)
Parameters
Ter
Definition
m
A date in datetime or text format, containing the year you
date
want to find.
Return value
An integer in the range 1900-9999.
Example:
=YEAR(TODAY())
=YEAR("March 2007")
YEARFRAC
Calculates the fraction of the year represented by the number of whole days between
two dates. Use the YEARFRAC worksheet function to identify the proportion of a whole
year's benefits or obligations to assign to a specific term.
Syntax
DAX Copy
YEARFRAC(<start_date>, <end_date>, <basis>)
Parameters
Term Definition
start_da
The start date in datetime format.
te
end_dat
The end date in datetime format.
e
(Optional) The type of day count basis to use. All arguments are truncated
to integers.
Basis - Description
0 - US (NASD) 30/360
basis
1 - Actual/actual
2 - Actual/360
3 - Actual/365
4 - European 30/360
Return value
A decimal number. The internal data type is a signed IEEE 64-bit (8-byte) double-
precision floating-point number.
Example:
=YEARFRAC("Jan 1 2007","Mar 1 2007")
=YEARFRAC(Orders[TransactionDate],Orders[ShippingDate])
