Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 56 ยท DAX

DAX Date and Time Functions

Learn DAX date and time functions: CALENDAR, DATEDIFF, EDATE, EOMONTH, WEEKDAY, WEEKNUM, NOW, UTCNOW and YEARFRAC, with runnable Python stand-ins.

Dates are the most important dimension in almost every report, and DAX has a large toolbox for them. Some functions build a calendar, some pick a date apart, some add months or find a month end, and some measure the distance between two dates. Date Tables and Time Intelligence Setup showed how to build a date table, and Time Intelligence used the functions that move filters. This lesson covers the scalar date functions: the ones that take dates and return a date or a number.

You will meet each function with its exact behavior, the traps (one of them makes ages wrong by a year), and Python stand-ins that reproduce the results so you can check your own understanding.

DAX cannot run on this site, so the DAX is read-only. The Python examples reproduce what the functions return. The stand-ins use Python's datetime, and pandas for month arithmetic. They are not Power BI itself.

Building dates: DATE, CALENDAR, CALENDARAUTO

DAX
Order Date = DATE ( 2026, 3, 5 )

Date Table = CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) )

Date Auto  = CALENDARAUTO ( )
Date FY    = CALENDARAUTO ( 6 )

DATE(year, month, day) is the safe way to make a date, because it never depends on regional settings. It also rolls over: DATE(2026, 14, 1) is 1 February 2027.

CALENDAR(start, end) returns a one-column table named Date with one row per day. CALENDARAUTO() scans every date column in the model, finds the earliest and latest years and returns whole calendar years between them. The optional argument is the last month of your fiscal year: CALENDARAUTO(6) returns July to June years. Be careful with CALENDARAUTO: one stray date such as 1900-01-01 (a common placeholder) or a far-future end date stretches the table by decades. Prefer CALENDAR with explicit bounds, or bounds taken from your data with MIN and MAX of the fact table.

Taking dates apart

DAX
Year      = YEAR ( 'Date'[Date] )
Month     = MONTH ( 'Date'[Date] )
Day       = DAY ( 'Date'[Date] )
Quarter   = QUARTER ( 'Date'[Date] )
Weekday   = WEEKDAY ( 'Date'[Date], 2 )
Week No   = WEEKNUM ( 'Date'[Date], 21 )

WEEKDAY and WEEKNUM have a second argument that changes the convention, and the default is the American one.

Function Return type Meaning
WEEKDAY(d, 1) (default) 1 to 7 Sunday = 1 ... Saturday = 7
WEEKDAY(d, 2) 1 to 7 Monday = 1 ... Sunday = 7
WEEKDAY(d, 3) 0 to 6 Monday = 0 ... Sunday = 6
WEEKNUM(d, 1) (default) 1 to 54 weeks start Sunday; week 1 contains 1 January
WEEKNUM(d, 2) 1 to 54 weeks start Monday; week 1 contains 1 January
WEEKNUM(d, 21) 1 to 53 ISO 8601 week numbers

Pick 21 for WEEKNUM when your business reports by ISO week. Without it, the last days of December can be "week 53" and the first days of January "week 1" of the same ISO week, which splits a week across two numbers.

Moving by months: EDATE and EOMONTH

DAX
Renewal    = EDATE ( Contracts[Start], 12 )
Month End  = EOMONTH ( Orders[Date], 0 )
Next Month End = EOMONTH ( Orders[Date], 1 )
Due Date   = EOMONTH ( Invoices[Date], 1 ) + 15

EDATE(date, months) adds whole months (negative goes back) and keeps the day of the month where it can. If the target month is shorter, it clamps to the last day: EDATE(31 Jan, 1) is 28 February (29 in a leap year). EOMONTH(date, months) returns the last day of the month that is months away; with 0 it is the end of the current month. The "net 30 from end of month" rule above is EOMONTH(...) + 30, because dates can be added with plain +.

Distance between dates: DATEDIFF and YEARFRAC

DAX
Days Open   = DATEDIFF ( Tickets[Opened], Tickets[Closed], DAY )
Months Open = DATEDIFF ( Tickets[Opened], Tickets[Closed], MONTH )
Age Years   = DATEDIFF ( Customers[Birth Date], TODAY ( ), YEAR )
Fraction    = YEARFRAC ( Contracts[Start], Contracts[End], 1 )

DATEDIFF(start, end, interval) supports SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER and YEAR. The crucial thing to know: it counts the boundaries crossed, not complete intervals. From 31 December to 1 January is one day and also one year, because the year boundary was crossed. So DATEDIFF(birth, TODAY(), YEAR) is wrong for ages whenever the birthday has not yet happened this year.

A correct age subtracts one when the birthday has not arrived yet:

DAX
Age =
VAR Years = DATEDIFF ( Customers[Birth Date], TODAY ( ), YEAR )
VAR HadBirthday =
    DATE ( YEAR ( TODAY ( ) ), MONTH ( Customers[Birth Date] ), DAY ( Customers[Birth Date] ) ) <= TODAY ( )
RETURN
    Years - IF ( HadBirthday, 0, 1 )

(If the birth date is 29 February, treat the case separately, because DATE(2027, 2, 29) rolls into March.) YEARFRAC(start, end, basis) returns years as a decimal and its basis argument chooses the day-count convention: 0 US 30/360 (default), 1 actual/actual, 2 actual/360, 3 actual/365, 4 European 30/360. Finance teams care about this choice because it changes interest and prorated amounts.

Try it: boundaries, months and age

Python Stand-in: DATEDIFF counts boundaries, EDATE clamps

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The two people born on 4 and 6 March 1990 show the difference: on 5 March 2026 the first is 36, the second is still 35, but the naive formula says 36 for both.

Weekdays and week numbers

Python Stand-in: WEEKDAY and WEEKNUM conventions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

1 January 2026 is a Thursday. The default WEEKNUM calls it week 1 and so does ISO, but the end of December can differ between the two schemes, which is why you should decide on one convention for the whole model.

Now and today, and the time zone trap

DAX
Refreshed At = NOW ( )
Today        = TODAY ( )
UTC Now      = UTCNOW ( )
UTC Today    = UTCTODAY ( )
Local Now    = UTCNOW ( ) + TIME ( 3, 0, 0 )

NOW() and TODAY() return the date and time of the machine that evaluates the formula. In Power BI Desktop that is your computer, but in the service it is a server that uses UTC. A report that shows "today" correctly on your laptop can show tomorrow's or yesterday's date in the browser for people in other time zones. UTCNOW() and UTCTODAY() are explicit about it, and you add a fixed offset to get a local time: UTCNOW() + TIME(3, 0, 0) for UTC+3. A fixed offset ignores daylight saving changes, so if exactness matters, convert in Power Query with DateTimeZone.SwitchZone when the data loads (see Data Types and Conversions in Power BI).

These functions are volatile in the sense that they change on every refresh or query. For "last refresh time", do not use NOW() in a measure, because it shows the time of each query. Create a small table in Power Query with DateTime.LocalNow() so the stored value reflects the refresh.

Text to date: DATEVALUE and TIMEVALUE

DATEVALUE("2026-03-05") converts a text value to a date. The ISO form yyyy-MM-dd is the only one guaranteed to work in every regional setting. "03/05/2026" can be 5 March or 3 May depending on the locale. If the source delivers dates as text, convert them in Power Query with an explicit culture instead.

Try it: prorating a contract with YEARFRAC

Python Stand-in: YEARFRAC day-count conventions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The three conventions give 0.5000, 0.5028 and 0.4959 for the same six months. For a yearly fee of 12,000 that is 6,000, 6,033 and 5,951: a small difference in each row that adds up over a portfolio, so agree the convention with finance and write it in the measure's description.

Practical advice

  • Use DATE (or ISO text) to build dates, never a locale-dependent string.
  • Keep one week convention in the model, ideally stored as columns in the date table, not recalculated in measures.
  • For ages and tenures, do not rely on DATEDIFF(..., YEAR) alone.
  • Use UTCNOW() plus an offset (or convert in Power Query) when the report is read in several time zones.
  • Scalar functions work row by row, so put them in calculated columns on the date table; measures that move whole periods belong to Time Intelligence.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does DATEDIFF(DATE(2025,12,31), DATE(2026,1,1), YEAR) return?

    DATEDIFF counts boundaries, not complete intervals. That is why a plain DATEDIFF with YEAR gives the wrong age before the birthday.
  2. 2. What is EDATE(DATE(2026,1,31), 1)?

    EDATE keeps the day where it can and clamps to the last day of the shorter month.
  3. 3. Which call returns ISO 8601 week numbers?

    Return type 21 is the ISO system, where weeks start on Monday and week 1 contains the first Thursday of the year.
  4. 4. A card with TODAY() shows tomorrow's date for users in New York when the report is open in the service. Why?

    The service evaluates TODAY() in UTC. Use UTCNOW() with an offset, or convert in Power Query.
  5. 5. Why is CALENDARAUTO risky?

    CALENDARAUTO uses every date column in the model. Explicit bounds in CALENDAR avoid surprises.

Summary

  • DATE builds safe dates; CALENDAR makes a date table from explicit bounds, CALENDARAUTO scans the model and can overshoot.
  • WEEKDAY and WEEKNUM take a convention argument; use WEEKNUM(d, 21) for ISO weeks.
  • EDATE and EOMONTH move by months and clamp to month ends.
  • DATEDIFF counts boundaries crossed, so calculate ages with a birthday check; YEARFRAC depends on the day-count basis.
  • NOW() and TODAY() follow the evaluating machine; in the service that means UTC.

Next: DAX Logical and Information Functions.