The DATEADD function returns a table containing a column of dates that match those involved in the current context, shifted forward or backward by a specified number of intervals.
DATEADD(
dates,
number_of_intervals,
interval
)
- dates: Column containing dates.
- number_of_intervals: Number of intervals to move forward or backward in time the period involved in the current context.
- interval: Type of interval.
The DATEADD function returns a table.
The dates argument can be a reference to a column containing dates, an expression that returns a table with a single column containing dates, or a Boolean expression that defines a table with a single column containing dates.
If the number_of_intervals argument is a positive number, the interval indicated by the last argument, interval, will be considered forward in time. If it is negative, it will be considered backwards in time.
Possible values for the interval argument are: DAY, MONTH, QUARTER, or YEAR, and must be in uppercase and without quotation marks.
The result returned by the function will include only dates present in the dates column.
If the number_of_intervals argument is not an integer, it is rounded to the nearest integer. In this way, the expression
DATEADD(DimDate[Datekey], -1.6, MONTH)
It is equivalent to
DATEADD(DimDate[Datekey], -2, MONTH)
If a year is a leap year and, therefore, its month of February has 29 days, the month corresponding to the previous year or the next one will only have 28 days (since that year will not be a leap year). Something similar happens with a month compared to the previous month or the next month: the number of days is never the same, although the function correctly returns the entire month.
It is noteworthy that the DATEADD function can show a special behavior when working with weeks: we will find that neither the weeks have all 7 days, nor a period with a certain number of days in the initial context corresponds to a period with the same number of days after applying the function, nor does it consider the same days as part of the same week before and after applying the function (thus, January 7 of a year can be considered part of week 1 or part of week 2 if the DATEADD function is applied to add or subtract a year to the context).
The DATEADD function requires that the time period to be considered is a continuous set of days. This means that in certain cases we might encounter unexpected errors: A scenario in which this occurs is when we take the years to the columns of a matrix and the months to the rows. In these circumstances, the subtotals also assume the calculation of the expression, and the row subtotals would be trying to apply the expression to the set of months of January, to the set of months of February, etc., periods that are not continuous. The solution is simply to hide the row subtotals, which must be done before adding the expression containing the DATEADD function.
If, given a table with sales, we define the measure:
Ventas = SUM(FactSales[SalesAmount])
...defining the total sales, and the measure
Ventas mes anterior = CALCULATE(
[Ventas],
DATEADD(DimDate[Datekey], -1, MONTH)
)
...defining the sales made a month before, and we take both measures to the head of columns in a matrix with the months in rows, the result is the following:
It can be seen how the "Ventas mes anterior" measure returns, as expected, the sales corresponding to the month prior to the one involved in the context.
