DAX Date & Time Functions
Date and time functions are crucial for time-based analysis — one of the most common requirements in business intelligence.
Basic Date Functions
TODAY() and NOW()
| Function | Returns | Example |
|---|---|---|
TODAY() | Current date (no time) | 2025-02-17 |
NOW() | Current date and time | 2025-02-17 10:30:45 |
Example — Days Since Order:
Days Since Order = TODAY() - Sales[OrderDate]YEAR(), MONTH(), DAY()
Extract specific parts from a date.
| Function | Extracts | Example (for 2025-02-17) |
|---|---|---|
YEAR() | Year | 2025 |
MONTH() | Month number | 2 |
DAY() | Day of month | 17 |
WEEKDAY() | Day of week (1=Sun) | 2 (Monday) |
WEEKNUM() | Week number of year | 8 |
HOUR() | Hour from time | 10 |
MINUTE() | Minute from time | 30 |
Example:
Order Year = YEAR(Sales[OrderDate])
Order Month = MONTH(Sales[OrderDate])DATE()
Creates a date from year, month, and day values.
Syntax: DATE(Year, Month, Day)
Example:
Start Date = DATE(2025, 1, 1)DATEADD()
Returns a table of dates shifted by a specified interval.
Syntax: DATEADD(Dates, NumberOfIntervals, Interval)
Intervals: DAY, MONTH, QUARTER, YEAR
Examples:
Last Year Sales = CALCULATE([Total Sales], DATEADD(Dates[Date], -1, YEAR))Last Month Sales = CALCULATE([Total Sales], DATEADD(Dates[Date], -1, MONTH))DATEDIFF()
Returns the difference between two dates in a specified unit.
Syntax: DATEDIFF(StartDate, EndDate, Interval)
Intervals: DAY, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND
Examples:
Order Age (Days) = DATEDIFF(Sales[OrderDate], TODAY(), DAY)Customer Tenure (Months) = DATEDIFF(Customers[JoinDate], TODAY(), MONTH)EOMONTH()
Returns the last day of the month, N months from the given date.
Syntax: EOMONTH(StartDate, Months)
Example:
End of Current Month = EOMONTH(TODAY(), 0)
End of Next Month = EOMONTH(TODAY(), 1)
End of Previous Month = EOMONTH(TODAY(), -1)CALENDAR() and CALENDARAUTO()
Generate a date table.
CALENDAR():
DateTable = CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31))CALENDARAUTO():
DateTable = CALENDARAUTO()Automatically detects date range from your data.
Quick Reference
| Function | Purpose | Example |
|---|---|---|
TODAY() | Current date | TODAY() |
NOW() | Current date/time | NOW() |
YEAR() | Extract year | YEAR([Date]) |
MONTH() | Extract month | MONTH([Date]) |
DATE() | Create a date | DATE(2025,1,1) |
DATEADD() | Shift dates | DATEADD(Dates[Date],-1,YEAR) |
DATEDIFF() | Difference between dates | DATEDIFF([Start],[End],DAY) |
EOMONTH() | End of month | EOMONTH(TODAY(),0) |
CALENDAR() | Generate date table | CALENDAR(start, end) |