Work with dates

Date references let formulas move through time, group dates, and calculate period boundaries.

Use the table’s granularity

Write Date with the grain used by the formula:

Date.Month
Date.Quarter
Date.Year

Supported grains are Day, Week, Month, Quarter, Half, and Year.

Reference a nearby period

The compact lookup uses the formula’s current Date grain:

Revenue[-1]

At monthly grain, this reads Revenue from the previous month. A positive value moves forward. For an explicit date shift, use dateadd:

dateadd(Date.Month, -1, "month")

Supported units are day, week, month, quarter, half, and year.

Select a fixed period

Revenue[Date.Month = "2026-01"]
Revenue[Date.Quarter = "2026-Q1"]
Revenue[Date.Year = "2026"]

Select a time window

Use an inclusive range when a calculation spans several periods:

sum(Revenue[Date.Month in "2026-01":"2026-12"])

Relative endpoints move with the current period:

sum(Revenue[Date.Month in [-12]:[-1]])

Find a period boundary

startofmonth(Date.Month)
startofquarter(Date.Month)

startofday, startofweek, startofhalf, and startofyear are also available. See Date-truncation functions.

Read part of a date

year(Date.Month)
month(Date.Month)
quarter(Date.Month)
half(Date.Month)

Week functions include weekday, weeknum, and isoweeknum. See Date-part functions.

Calculate a period end or date difference

eomonth(Date.Month, 0)
datedif(`Start Date`, Date.Month, "M")

Build a date

date(year(Date.Month), 12, 31)

Formula ranges

Actuals and Forecast also depend on Date. A formula assigned to a formula range must include the Model’s Date grain in its scope.

Get started

Work with Ari

Formulas

Integrations

Scenarios

Dashboards

Automation

Headcount

Workspace settings

Use the table’s granularity

Reference a nearby period

Select a fixed period

Select a time window

Find a period boundary

Read part of a date

Calculate a period end or date difference

Build a date

Formula ranges

​Use the table’s granularity

​Reference a nearby period

​Select a fixed period

​Select a time window

​Find a period boundary

​Read part of a date

​Calculate a period end or date difference

​Build a date

​Formula ranges

​Related

Use the table’s granularity

Reference a nearby period

Select a fixed period

Select a time window

Find a period boundary

Read part of a date

Calculate a period end or date difference

Build a date

Formula ranges

Related