AXEL Date & Time Functions
📅 Date & Time Intelligence (26 Functions)
Comprehensive time-based analysis with these powerful functions.
Date Construction & Extraction
DATE
Create a date from year, month, day components.
DATE(2024, 12, 25)
Returns: December 25, 2024
Use Cases: Dynamic date creation, calculations
YEAR / MONTH / DAY
Extract date components.
YEAR(DATE(2024, 6, 15)) -- Returns: 2024
MONTH(DATE(2024, 6, 15)) -- Returns: 6
DAY(DATE(2024, 6, 15)) -- Returns: 15
HOUR / MINUTE / SECOND
Extract time components.
HOUR([DateTime]) -- Returns: 0-23
MINUTE([DateTime]) -- Returns: 0-59
SECOND([DateTime]) -- Returns: 0-59
QUARTER / WEEKDAY
Period identification.
QUARTER(DATE(2024, 6, 15)) -- Returns: 2 (Q2)
WEEKDAY(DATE(2024, 6, 15)) -- Returns: 1-7
Date Calculations
DATEDIFF
Calculate difference between dates.
DATEDIFF(DATE(2024, 1, 1), DATE(2024, 12, 31), "DAY")
Returns: 365 (days between dates)
Units: "YEAR", "MONTH", "DAY", "HOUR", "MINUTE", "SECOND"
DATEADD
Add/subtract time intervals.
DATEADD([DateTable], 7, "DAY")
Use Cases: Date projections, period shifts
CALENDAR / CALENDARAUTO
Generate date tables.
CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31))
CALENDARAUTO() -- Auto-detects range
Time Intelligence - Cumulative
TOTALYTD / TOTALQTD / TOTALMTD
Calculate period-to-date totals.
TOTALYTD(SUM([Sales].Amount), [Date].Date)
TOTALQTD(SUM([Sales].Amount), [Date].Date)
TOTALMTD(SUM([Sales].Amount), [Date].Date)
DATESYTD / DATESMTD
Get period-to-date dates.
DATESYTD([Date].Date)
DATESMTD([Date].Date)
Period Functions
DATESBETWEEN
Dates within specific range.
DATESBETWEEN([Date].Date, DATE(2024, 1, 1), DATE(2024, 1, 31))
DATESINPERIOD
Moving time windows.
DATESINPERIOD([Date].Date, MAX([Date].Date), -30, "DAY")
Use Cases: Rolling averages, lookback periods
Period Comparisons
SAMEPERIODLASTYEAR
Year-over-year comparison.
CALCULATE(
SUM([Sales].Amount),
SAMEPERIODLASTYEAR([Date].Date)
)
PREVIOUSMONTH / PREVIOUSYEAR
Sequential period comparison.
CALCULATE(
SUM([Sales].Amount),
PREVIOUSMONTH([Date].Date)
)
PARALLELPERIOD
Custom period offset.
CALCULATE(
SUM([Sales].Amount),
PARALLELPERIOD([Date].Date, -1, QUARTER)
)
Boundary Functions
FIRSTDATE / LASTDATE
Period boundaries.
FIRSTDATE([Date].Date) -- First date in context
LASTDATE([Date].Date) -- Last date in context
STARTOFMONTH / ENDOFMONTH
Month boundaries.
STARTOFMONTH(DATE(2024, 6, 15)) -- Returns: June 1, 2024
ENDOFMONTH(DATE(2024, 6, 15)) -- Returns: June 30, 2024
💡 Advanced Time Patterns
Rolling 12-Month Average
AVERAGEX(
DATESINPERIOD([Date].Date, MAX([Date].Date), -12, MONTH),
[MonthlyTotal]
)
Year-over-Year Growth %
DIVIDE(
[CurrentYearSales] - [PriorYearSales],
[PriorYearSales]
)
Fiscal Year Calculations
CALCULATE(
SUM([Sales].Amount),
DATESYTD([Date].Date, "6/30")
)