PARALLELPERIOD
Returns the whole parallel month, quarter or year, shifted by a number of intervals.
PARALLELPERIOD(<dates>, <number_of_intervals>, <interval>), where interval is MONTH, QUARTER or YEARReturns: A table of dates.
What it does
PARALLELPERIOD takes the visible dates, finds the full period they belong to, and moves that whole period by a number of intervals. Where DATEADD moves only the visible days, PARALLELPERIOD moves the entire month, quarter or year. Finance teams use it to compare a partial period, such as the month to date, with the whole of the prior period, or to read a full prior year next to the current year to date.
Syntax and arguments
- dates: the date column of the Calendar table. Required.
- number_of_intervals: how many periods to move. Use -1 for the period before. Required.
- interval: MONTH, QUARTER or YEAR. Required.
Example
The sales team wants the month-to-date revenue for October 1 through 15, set against the full revenue of September, as a benchmark for the whole month.
Revenue Full Prior Month =
CALCULATE([Total Revenue], PARALLELPERIOD('Calendar'[Date], -1, MONTH))
In a view of October 1 through 15, the measure returns all of September, not only September 1 through 15. Set it next to Revenue MTD to see how the current month is tracking against a full prior month.
Example: a second, different use
The full prior fiscal year, as a benchmark for the year to date. The measure returns the whole of the prior year even while the current year is partly complete.
Revenue Full Prior Year =
CALCULATE([Total Revenue], PARALLELPERIOD('Calendar'[Date], -1, YEAR))
Things to watch
- PARALLELPERIOD returns whole periods. That is the difference from DATEADD, which returns the same visible days shifted.
- It uses calendar months, quarters and years. For a fiscal year, use PREVIOUSYEAR with the year end date.
- A partial current year compared with a full prior year is not like for like. Use SAMEPERIODLASTYEAR when you want the same days.
- Needs contiguous dates in the Calendar, with no gaps.
In business terms
PARALLELPERIOD is used when a team wants a benchmark that is a full period: the last full month for a flash report, or the full prior year for a growth benchmark. It keeps the question clear, because the comparison is the whole of the other period.