🕒 DAX Date & Time Functions

Last Updated: 16th October 2025

Date and Time Functions in DAX are used to work with dates, times, and periods — like finding the current date, extracting day/month/year, or calculating time differences.

They’re super important for time-based reporting, like monthly sales, YTD (Year-to-Date), or weekly analysis.

🗣 Hinglish Tip: Ye functions hume “Date ke saath calculation” karne dete hain — jaise aaj ki date lena, last month ka data nikalna, ya kitne din ka difference hai.

✏️ Basic Date Functions

1. TODAY()

Returns today’s date (based on your system).

TodayDate = TODAY()

2. NOW()

Returns current date and time (based on your system).

NOW()

3. DATE(Year, Month, Day)

Create a date from year, month, and day.

StartDate = DATE(2025, 4, 1)

🗣 Tip: Useful when you want to set a fixed date for comparison.

4. YEAR(), MONTH(), DAY()

Extract parts from a date.

Year = YEAR(TODAY())
Month = MONTH(TODAY())
Day = DAY(TODAY())

5. HOUR(), MINUTE(), SECOND()

Extract time parts from a datetime value.

Hour = HOUR(NOW())
Minute = MINUTE(NOW())
Second = SECOND(NOW())

6. FORMAT(column, "format")

Format a date or datetime value. DDDD = Day Full Name DDD = Day Short Name DD = Day Number D = Day Number with leading zero MMMM = Month Full Name MMM = Month Short name YYYY = Year HH = Hour MM = Minute SS = Second

FormattedDate = FORMAT(TODAY(), "DD-MMM-YYYY")

Date Conversion & Creation Functions

7. DATEVALUE()

Converts a text date into a date type.

RealDate = DATEVALUE("2025-10-16")

8. TIMEVALUE()

Converts a text time into a time type.

RealTime = TIMEVALUE("09:30 AM")

9. TIME(Hour, Minute, Second)

Create a time from hour, minute, and second.

StartTime = TIME(9, 30, 0)

Date Calculation Functions

10. EDATE(StartDate, Months)

Shift a date forward or backward by X months.

NextMonth = EDATE(TODAY(), 1)
PrevMonth = EDATE(TODAY(), -1)

11. EOMONTH(StartDate, Months)

Find the end of the month for a given date.

MonthEnd = EOMONTH(TODAY(), 0)
PrevMonthEnd = EOMONTH(TODAY(), -1)

12. WEEKDAY(Date, [ReturnType])

Returns the day number of week (1–7).

WeekDayNum = WEEKDAY(TODAY())

1 means Sunday by default.You can change this using the optional ReturnType argument.

13. WEEKNUM(Date)

Returns week number of the year.

WeekNumber = WEEKNUM(TODAY())

14. DATEDIFF(StartDate, EndDate, Interval)

Finds the difference between two dates.

DaysBetween = DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY)
MonthsBetween = DATEDIFF(Sales[OrderDate], Sales[ShipDate], MONTH)

🗣 Hinglish Tip: Ye Excel ke “=End−Start” jaisa hi hai, par interval ke hisaab se deta hai (days, months, years).

Time Intelligence Helpers

15. CALENDAR(StartDate, EndDate)

Creates a table of continuous dates.

CalendarTable = CALENDAR(DATE(2024,1,1), DATE(2025,12,31))

16. CALENDARAUTO()

Automatically creates a date table based on model data range.

DateTable = CALENDARAUTO()

Best function to generate a Date Dimension Table.

⚙ Common Mistakes

  • Using + or - directly on text-based dates → convert first using DATEVALUE()
  • Forgetting to mark Date Table → Time Intelligence won’t work
  • Wrong WEEKDAY() setting → gives Sunday/Monday mismatch
← PreviousNext →