DAX Time Intelligence Functions

In this blog post, we are going to look at three DAX time intelligent functions. These DAX time intelligent functions are PARALLELPERIOD, DATEADD and SAMEPERIODLASTYEAR. I know many of you will say the functions sound very similar and that they have more or less the same functionality. This is not the point of this blog post but rather to show you how these functions differ from each other so that you will be able to know which function to use in different scenarios.

If you prefer to watch the YouTube video, rather than read the blog, please see the video below:

PARALLELPERIOD

We will first look at PARALLELPERIOD and its syntax.

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 or back in time.

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)

  • 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 value for interval can be one of the following, year, quarter or month.

We can see that PARALLELPERIOD look at a set of dates have a number of intervals that it looks backwards or forward and goes backwards or forward based on a set interval of a year, quarter or month. We will go over the syntax of our two remaining functions and then you look at some examples.

DATEADD

DATEADD returns a table that contains a column of dates, shifted either forward or backwards in time by the specified number of intervals from the dates in the current context.

DATEADD(<dates>,<number_of_intervals>,<interval>)

  • 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 value for interval can be one of the following, year, quarter, month or day.

We can see that DATEADD look at a set of dates have a number of intervals that it looks backwards or forward and goes backwards or forward based on a set interval of a year, quarter or month.

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.

SAMEPERIODLASTYEAR(<dates>)

  • Dates – A column that contains dates.

SAMEPERIODLASTYEAR returns a single column table of dates values. SAMEPERIODLASTYEAR is specific to a year interval where a syntax for the DATEADD and PARALLELPERIOD include other intervals such as quarter, month day.

Examples: SAMEPERIODLASTYEAR, PARALELLPERIOD and DATEADD

We will look at some examples to see how these three DAX time intelligent functions work. We will start with the PARALLELPERIOD function.

We have a sales table from which we would like to create our Total Sales for the corresponding ‘parallel period of previous years. We are first going to create a measure called ‘Total Sales’.

Total Sales = CALCULATE(SUM(Sales[Total Sales]))

All that ‘Total Sales’ is doing is that it SUM the ‘Total Sales’ column from our ‘Sales’ table.

We will create our PARALLELPERIOD  measure and call it ‘PARALLELPERIOD’. In this example, we want to return the Total Sales for the corresponding parallel period of previous years. To do this we are going to start with the CALCULATE function (please see our blog post on the CALCULATE function). The CALCULATE function requires an Expression and Filter input. Our expression will be our ‘Total Sales’ measure and we will Filter by our PARALLELPERIOD.

PARALELLPERIOD = CALCULATE([Total Sales], PARALLELPERIOD(‘Calendar’ [Date], -1, YEAR))

Let’s explain this function now.  The filter context here is PARALLELPERIOD. PARALLELPERIOD is looking at the Date from the ‘Calendar Table’. Next, we need to choose the number of intervals that we want to look back or forward to. We decide that we want to go one period (-1) back in this example and we decide that we want to go back in years.

Let’s visually see the result of our two measures by formatting our sales table by day.

We can see that our Total Sales measure give us a value for Total Sales of each day. Our PARALLELPERIOD function, however, gives us the Total Sales for the year as we selected the PARALLELPERIOD to be one year back. Let’s view the result of our measure by changing our table to a yearly view.

From the table, we can clearly see what the outcome of PARALLELPERIOD is. Let’s take the example as highlighted in our table. Total Sales in 2014 is $4 420 669 and the PARALLELPERIOD measure will display this value in the year 2015 as the corresponding sales value of the previous year, namely 2014. This should already give you a good indication of what the function is doing.

Let’s make use of our other measures as well now:

DATEADD = CALCULATE([Total Sales], DATEADD(‘Calendar’ [Date], -1, YEAR))

SAMEPERIODLASTYEAR = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(‘Calendar’ [Date]))

Note: that SAMEPERIODLASTYEAR looks a bit different than PARALLELPERIOD and DATEADD. This is because SAMEPERIODLASTYEAR only looks at the year. 

Let’s add our 2 new measures to our canvas.

We see as expected that the 3 measure all return the same value. Let’s look at our values now from a monthly perspective.

Let’s first look at PARALLELPERIOD. 

We see that PARALLELPERIOD still returns the entire years values. This is because we are still looking at intervals of a year for PARALLELPERIOD. Thus it basically removes all filters in the context filters and only returns the year even if you change your table to a monthly view.

DATEADD and SAMEPERIODLASTYEAR behave a bit differently.

DATEADD actually returns the monthly values although we stated yearly intervals and the same for SAMEPERIODLASTYEAR. What we can see here is that these two functions return the corresponding monthly values, but going back one year period.

Let’s change our table to display Total Sales by day and see the behaviour of our functions.

What we see from our table is that PARALELLPERIOD still behaves the same, but DATEADD and SAMEPERIODLASTYEAR have changed. These two functions have updated to the formatting of the table, wherein our previous example we looked at monthly sales, but in this table, we are looking at daily sales. Thus DATEADD and SAMEPERIODLASTYEAR are now looking at the same sales values of a year ago.

Let’s just dig a little bit deeper into the behaviour of these functions. We are going to change PARALLELPERIOD and DATEADD’s interval to Month.

PARALELLPERIOD = CALCULATE([Total Sales], PARALLELPERIOD(‘Calendar’ [Date], -1, MONTH))

DATEADD = CALCULATE([Total Sales], DATEADD(‘Calendar’ [Date], -1, MONTH))

Let’s look at the result:

What we see here is that PARALELLPERIOD is now returning the previous month value and the same for DATEADD.

Something to take note of is the behaviour of SAMEPERIODLASTYEAR. In the function, we only specify the dates. Thus this function will adapt to the way we look at our data as in the table in our example. If we look at the table on a yearly level, SAMEPERIODLASTYEAR will return the previous years values. If we look at the table on a monthly level, SAMEPERIODLASTYEAR will return the corresponding months value for looking one year back and the same rule applies when we filter our table on a daily level.

I hope that you have a good idea now on how these DAX time intelligent functions behave and what the use case for each function is.

To learn more about using Power BI, view our training page and enrol.