The PARALLELPERIOD function returns a table containing a column of dates that match those involved in the current context, shifted forwards or backwards by a specified number of intervals but extended to the specified interval.
PARALLELPERIOD(
dates,
number_of_intervals,
interval
)
- dates: Column containing dates.
- number_of_intervals: Number of intervals to shift forward or backward in time the period involved in the current context.
- interval: Type of interval.
The PARALLELPERIOD 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 will be considered forward in time. If it is negative, it will be considered backward in time.
Possible values for the interval argument are: MONTH, QUARTER or YEAR, and they must be written in uppercase and without quotes.
The result returned by the function will include only dates included 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:
PARALLELPERIOD(DimDate[Datekey], -1.6, MONTH)
...is equivalent to:
PARALLELPERIOD(DimDate[Datekey], -2, MONTH)
This function considers the first and last days of the current context, shifts them in time by the specified number of intervals, considers the smallest interval (month, quarter or year) that includes this period, and returns all dates in that interval. If the time period involved in the context is a subset of the specified interval, the function will add all the dates necessary to complete the interval. So, for example, if the specified interval is "MONTH", the number of intervals is -1 (that is, one interval in the past) and the periods considered in the context are months, the function will return, as you would expect, the dates corresponding to the previous month. Conversely, if the specified interval is "YEAR" but the period considered in the context is months, the function will return the entire previous year, not just the dates of the month before the one implied in the context. That is, the PARALLELPERIOD function will always return a set of dates equivalent to the specified interval (month, quarter or year). See several examples below.
In the event that the current context is not a fraction of a month, quarter or year, the result of this function is difficult to interpret. Thus, if the current context is the period corresponding to the nth week of the year (weeks that sometimes fall part in one month and part in another), the interval is months and the number of intervals is, for example, -1, only weeks that include the end of the month receive a non-empty value as a result of this function (and not all weeks that include the end of the month receive a non-empty value).
If, given a table with sales, we define the measure:
Sales = SUM(FactSales[SalesAmount])
...calculating the total sales, and the measure:
Ventas un mes anterior = CALCULATE(
[Ventas],
PARALLELPERIOD(DimDate[Datekey], -1, MONTH)
)
We can take the list of months to a matrix showing both measures:
You can see how the PARALLELPERIOD function returns the time period for the previous month, just as you would expect.
In the above scenario, if we define the measure
Ventas un año atrás = CALCULATE(
[Ventas],
PARALLELPERIOD(DimDate[Datekey], -1, YEAR)
)
And we take both measures to a matrix, the result is the following:
In order to confirm the figures returned by the last measure created, we can take the measure [Ventas] to a matrix placing the years at the head of each row:
In this way, it is confirmed that, in the first matrix of this example, the PARALLELPERIOD function does indeed return all the dates corresponding to the year prior to the one involved in the current context.
Finally, once the measure commented in the previous example is defined, if we take it to a matrix in which we have the years heading the rows, the result is the following:
In this case, since the time periods involved in the context (years) is not a subset of the specified interval (months), the function does not fill the period with additional dates and is limited to considering the period of the context (year) shifted by a month back. Thus, the figure 2,678,900,061.10 that is seen in the row corresponding to the year 2008 corresponds to the sales between December 2007 and November 2008 (that is, the year 2008 moved back one month). We can confirm this figure by showing sales per month and filtering the matrix appropriately to show only the period between December 2007 and November 2008:
Indeed, the total sales for this period is the same figure that has been commented.
