Same thing last year

Time Intelligence functions are often used for reviewing implementation of the goals at the same time last year. In what follows we will mention a few of DAX functions that cover this area. First, there is a function SAMEPERIODLASTYEAR which should monitor business results in the same period last year. And then there are functions DATEADD and PARALLELPERIOD by which we can monitor the achievement of objectives in the previous or next period (month, quarter, or year) in relation to the current data.

As before, in this post we’ll be using example that tracks results of a retail chain business in a certain time period. In the Model we have added and connected tables Artikli (Items), Lokacije (Locations) and Transakcije (Transactions). Also we have added Calendar table and connected it to table Transakcije. First, let’s create a measure that represents sum of all quantities within table Transakcije:

UkupnaProdaja:=SUM(Transakcije[KOL])

To track sales in the same period last year we’ll use homonymous function, which has a syntax:

SAMEPERIODLASTYEAR (<date column>)

Date column is a column with date in Calendar table. By nesting this function into CALCULATE we can create an expression which shows turnover in same period last year:

ProdajaSPLY:=CALCULATE(SUM(Transakcije[KOL]),SAMEPERIODLASTYEAR(‘Calendar'[Date]))

If we drag this measure in Power Pivot report we’ll get the report we wanted.

Instead of this function we could have used function PARALLELPERIOD. Its syntax is:

PARALLELPERIOD (<date column>,<number of periods>,<period name>)

Period name is a name of date period, which can be: YEAR, QUARTER or MONTH. Number of periods represents integer value that can be negative, if we’re tracking past periods, or positive, if we want to show future periods. Previous measure could have been written with use of PARALLELPERIOD, and an expression should have look like this:

ProdajaPP_YEAR:=CALCULATE(SUM(Transakcije[KOL]),PARALLELPERIOD(‘Calendar'[Date],-1,YEAR))

If we add this measure in report we can see that it shows sum of quantities for whole period. In this example, it is a whole year. If we have used MONTH and different values for number of periods, we could have track sales in previous or next month:

ProdajaPREM:=CALCULATE(SUM(Transakcije[KOL]),PARALLELPERIOD(‘Calendar'[Date],-1,MONTH))

ProdajaSLM:=CALCULATE(SUM(Transakcije[KOL]),PARALLELPERIOD(‘Calendar'[Date],1,MONTH))

Put in report, these measures show sales in previous and next month.

At the end let’s mention DATEADD function, which has a syntax:

DATEADD (<date column>,<number of periods>,<period name>)

It has similar purpose like previous function. The difference is that DATEADD do not cover whole period (month, quarter, year) but period between two given dates.