TIME INTELLIGENCE FUNCTION IN POWER BI
Time is the most important dimension in data analysis, since it helps us to compare trends, patterns, and performance across various time periods. Time Intelligence is the process of analyzing and comparing sets of data that cover different time periods, e.g. days, months, quarters, and years. Time Intelligence DAX Functions allow users to perform dynamic calculations on the base of dates context, for example, year-to-date totals, moving averages, and year-over-year comparisons.
So, let's demystify these functions with examples:
DATESYTD - Returns a table that contains a column of the dates for the year to date, in the current context.
Syntax : DATESYTD(<dates> [,<year_end_date>])
Parameters :
dates : A column that contains dates
year_end_date : (optional) A literal string with a date that defines the year end date. The default is December 31
Example :
DATESQTD - Returns a table that contains a column of the dates for the quarter to date, in the current context.
Syntax : DATESQTD(<dates>)
Parameters :
dates : A column that contains dates
Example :
DATESMTD - Returns a table that contains a column of the dates for the month to date, in the current context.
Syntax : DATESMTD(<dates>)
Parameters :
dates : A column that contains dates
Example :
TOTALYTD - Evaluates the year-to-date value of the expression in the current context.
Syntax : TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])
Parameters :
expression : An expression that returns a scalar value
dates : A column that contains dates
filter : (optional) An expression that specifies a filter to apply to the current context
year_end_date : (optional) A literal string with a date that defines the year end date. The default is December 31
Example :
TOTALQTD - Evaluates the value of the expression for the dates in the quarter to date, in the current context.
Syntax : TOTALQTD(<expression>,<dates>[,<filter>])
Parameters :
expression : An expression that returns a scalar value
dates : A column that contains dates
filter : (optional) An expression that specifies a filter to apply to the current context
Example :
TOTALMTD - Evaluates the value of the expression for the month to the date, in the current context.
Syntax : TOTALMTD(<expression>,<dates>[,<filter>])
Parameters :
expression : An expression that returns a scalar value
dates : A column that contains dates
filter : (optional) An expression that specifies a filter to apply to the current context
Example :
SAMEPERIODLASTYEAR - Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.
Syntax : SAMEPERIODLASTYEAR(<dates>)
Remarks :
The dates argument can be any of the following:
A reference to a date/time column,
A table expression returns a single column of date/time values ,
A Boolean expression that defines a single column table of date/time values
The dates returned are the same as the dates returned by this equivalent formula: DATEADD(dates,-1,year)
This function is not supported for use in Direct Query mode when used in calculated columns or row level security rules.
Parameters :
dates : A column that contains dates
Example :
PARALLELPERIOD - Returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time of back in time,
Syntax : PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)
Parameters :
dates : A column that contains dates
number_of_intervals : An interger that specifies the number of intervals to add to or subtract from the dates
interval : The interval by which to shift the dates. The values for interval can be one of the year, quarter or month
Remarks :
This function takes the current set of dates in the column specified by dates, shifts the first date and the last date the specified number of intervals, and then returns all contiguous dates between the two shifted dates. If the interval is a partial range of month, quarter or year then any partial months in the result are also filled out to complete the entire interval.
The interval parameter is an enumeration, not a set of strings, therefore vlaues should not be enclosed in quotation marks.
The PARALLELPERIOD function is similar to the DATEADD function except that PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns.
This function is not supported for use in Direct Query mode when used in calculated columns or row level security rules.
Example :
DATEADD - Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.
Syntax : DATEADD(<dates>,<number_of_intervals>,<interval>)
Parameters :
dates : A column that contains dates
number_of_intervals : An integer that specifies the number of intervals to add to or subtract from the dates
interval : The interval by which to shift the dates. The values for interval can be one of the year, quarter, month or day
Example :
DATESINPERIOD - Returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date intervals.
Syntax : DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)
Parameters :
dates : A column that contains dates
start_date : A date expression
number_of_intervals : An integer that specifies the number of intervals to add to or subtract from the dates
interval : The interval by which to shift the dates. The values for interval can be one of the year, quarter, month or day
Example :
DATESBETWEEN - Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.
Syntax : DATESBETWEEN(<Dates>, <StartDate>, <EndDate>)
Parameters :
dates : A column that contains dates
start_date : A date expression
EndDate : A date expression
Remarks :
If StartDate is blank, then StartDate will be the earliest value in the dates column
If EndDate is blank, the EndDate will be the latest value in the dates column
This function is not supported for use in Direct Query mode when used in calculated columns or row level security rules.
Example :
