Fundamental difference between DATEADD a PARALLELPERIOD functions (DAX – Power Pivot, Power BI)

These two functions may seem similar to someone – because they are used in situations that may seem similar. So what do DATEADD and PARALLELPERIOD have in common? Both, for some period, assign values for some other period. Like five months forward, three quarters backward etc. It works for days, months, quarters and years.

So how do these functions differ?

DATEADD

DATEADD shows always the equally long period, but moved backwards or forwards by some number of years (quarters, months, days…).

For example if you move two quarters backward:

On the quarter level it shows the quarter values moved back, so for example for the fourth quarter it shows the second quarter. On the month level it shows the month values two quarters back – so for September it shows March etc.

PARALLELPERIOD

PARALLELPERIOD shows always the length of period you define in syntax (year, quarter, month…), regardless of context.

For example if you move two quarters backward:

For the fourth quarter it returns values from the second quarter, but for September it returns values, still, from the second quarter (because second quarters includes March, which is two quarters before September).

Example

Functions can be written like this:

DATEADD

  • Two quarters ago DATEADD = CALCULATE(
       SUM(MyTable[MyNumbers]);
       DATEADD(
               MyCalendar[DateInTimeDimension];
           -2;
           QUARTER))

PARALLELPERIOD

  • Two quarters ago PARALLELPERIOD = CALCULATE(
       SUM(MyTable[MyNumbers]);
       PARALLELPERIOD(
         MyCalendar[DateInTimeDimension];
         -2;
         QUARTER))

There are more functions using similar logic:

  • The same logic as DATEADD is used by SAMEPERIODLASTYEAR (which has less arguments, but always moves exactly one step back).
  • The same logic as PARALLELPERIOD is used by functions like PREVIOUS… and NEXT:.. (they are also easier but single – purpose only).