Time Intelligence Functions
Time intelligence functions support calculations to compare and aggregate data over time periods, supporting days, months, quarters, and years.
- All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year. For example, if the fiscal year 2008 starts on July 1, 2007, then the Date table must include all the days from July 1, 2007 to June 30, 2008.
- There needs to be a column with a DateTime or Date data type containing unique values. This column is usually called Date. Even though the Date column is often used to define relationships with other tables, this is not required. Still, the Date column must contain unique values and should be referenced by the Mark as Date Table feature. In case the column also contains a time part, no time should be used – for example, the time should always be 12:00 am.
- The Date table must be marked as a date table in the model, in case the relationship between the Date table and any other table is not based on the Date.
Function | Description |
---|---|
CLOSINGBALANCEMONTH | Evaluates the specified expression for the date corresponding to the end of the current month after applying specified filters. |
CLOSINGBALANCEQUARTER | Evaluates the specified expression for the date corresponding to the end of the current quarter after applying specified filters. |
CLOSINGBALANCEYEAR | Evaluates the specified expression for the date corresponding to the end of the current year after applying specified filters. |
DATEADD | Moves the given set of dates by a specified interval. |
DATESBETWEEN | Returns the dates between two given dates. |
DATESINPERIOD | Returns the dates from the given period. |
DATESMTD | Returns a set of dates in the month up to the last date visible in the filter context. |
DATESQTD | Returns a set of dates in the quarter up to the last date visible in the filter context. |
DATESYTD | Returns a set of dates in the year up to the last date visible in the filter context. |
ENDOFMONTH | Returns the end of month. |
ENDOFQUARTER | Returns the end of quarter. |
ENDOFYEAR | Returns the end of year. |
FIRSTDATE | Returns first non blank date. |
FIRSTNONBLANK | Returns the first value in the column for which the expression has a non blank value. |
FIRSTNONBLANKVALUE | Returns the first non blank value of the expression that evaluated for the column. |
LASTDATE | Returns last non blank date. |
LASTNONBLANK | Returns the last value in the column for which the expression has a non blank value. |
LASTNONBLANKVALUE | Returns the last non blank value of the expression that evaluated for the column. |
NEXTDAY | Returns a next day. |
NEXTMONTH | Returns a next month. |
NEXTQUARTER | Returns a next quarter. |
NEXTYEAR | Returns a next year. |
OPENINGBALANCEMONTH | Evaluates the specified expression for the date corresponding to the end of the previous month after applying specified filters. |
OPENINGBALANCEQUARTER | Evaluates the specified expression for the date corresponding to the end of the previous quarter after applying specified filters. |
OPENINGBALANCEYEAR | Evaluates the specified expression for the date corresponding to the end of the previous year after applying specified filters. |
PARALLELPERIOD | Returns a parallel period of dates by the given set of dates and a specified interval. |
PREVIOUSDAY | Returns a previous day. |
PREVIOUSMONTH | Returns a previous month. |
PREVIOUSQUARTER | Returns a previous quarter. |
PREVIOUSYEAR | Returns a previous year. |
SAMEPERIODLASTYEAR | Returns a set of dates in the current selection from the previous year. |
STARTOFMONTH | Returns the start of month. |
STARTOFQUARTER | Returns the start of quarter. |
STARTOFYEAR | Returns the start of year. |
TOTALMTD | Evaluates the specified expression over the interval which begins on the first of the month and ends with the last date in the specified date column after applying specified filters. |
TOTALQTD | Evaluates the specified expression over the interval which begins on the first day of the quarter and ends with the last date in the specified date column after applying specified filters. |
TOTALYTD | Evaluates the specified expression over the interval which begins on the first day of the year and ends with the last date in the specified date column after applying specified filters. |
Last update: Oct 3, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo