Date and Time DAX Functions

In this topic, I will cover various Date and Time DAX functions available in Power BI. Please find the Power BI file used in this post here.

Let’s look all the Date and Time DAX functions

Function	Description
CALENDAR	Returns a table with a single column named “Date” that contains a contiguous set of dates.
CALENDARAUTO	Returns a table with a single column named “Date” that contains a contiguous set of dates.
DATE	Returns the specified date in DateTime format.
DATEDIFF	Returns the count of interval boundaries crossed between two dates.
DATEVALUE	Converts a date in the form of text to date in DateTime format.
DAY	Returns the day of the month, a number from 1 to 31.
EDATE	Returns the date that is the indicated number of months before or after the start date.
EOMONTH	Returns the date in the DateTime format of the last day of the month, before or after a specified number of months.
HOUR	Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
MINUTE	Returns the minute as a number from 0 to 59, given a date and time value.
MONTH	Returns the month as a number from 1 (January) to 12 (December).
NOW	Returns the current date and time in DateTime format.
QUARTER	Returns the quarter as a number from 1 to 4.
SECOND	Returns the seconds of a time value, as a number from 0 to 59.
TIME	Converts hours, minutes, and seconds given as numbers to a time in DateTime format.
TIMEVALUE	Converts a time in text format to a time in DateTime format.
TODAY	Returns the current date.
UTCNOW	Returns the current UTC date and time
UTCTODAY	Returns the current UTC date.
WEEKDAY	Returns a number from 1 to 7 identifying the day of the week of a date.
WEEKNUM	Returns the week number for the given date and year according to the return_type value.
YEAR	Returns the year of a date as a four-digit integer in the range 1900-9999.
YEARFRAC	Calculates the fraction of the year represented by the number of whole days between two dates.

---

Let’s go over some of the most commonly used Date and Time DAX functions

---

CALENDAR

Syntax: CALENDAR(StartDate, EndDate)

In the data view, we can add a new table using this DAX function. It returns one column with contiguous dates. We need to specify the start date and the end date within the DAX function.

Table-Date = CALENDAR(DATE(2019, 1, 1), DATE(2022, 3, 11))

The above code returns a column with date values starting 01/01/2019 till 03/11/2022

---

CALENDARAUTO

Syntax: CALENDARAUTO([fiscal_year_end_month])

In the data view, we can add a new table using this DAX function. It returns one column with contiguous dates. The range of dates is calculated automatically based on data in the model.

CALENDARAUTO-TABLE = CALENDARAUTO()

The above code returns a column with date values starting 01/01/2019 till 12/31/2022

---

DATE

Syntax: DATE(<year>, <month>, <day>)

This DAX can be used in combination with other DAX functions or can be used separately as a measure or a column. It returns a specific date depending on the variables.

DATE-Example = DATE(2019, 1, 2)

The above code returns 01/02/2019

---

DATEDIFF

Syntax: DATEDIFF(<start_date>, <end_date>, <interval>)

This DAX returns the difference between the start date and the end date in the specified interval. It is very useful when creating calculated measures which rely on time intervals.

Various intervals available are:

• Second
• Minute
• Hour
• Day
• Week
• Month
• Quarter
• Year

DATEDIFF-Example = DATEDIFF(DATE(2020, 1, 1), DATE(2020, 2, 1), DAY)

The above code returns 31

---

Hopefully, this article is useful to get a basic understanding of various DAX functions available in Power BI. I will update the post as I come across more use cases for the Date and Time DAX functions. Feel free to leave a response or reach out to me for suggestions.