DAX SAMEPERIODLASTYEAR Function

SAMPLEPERIODLASTYEAR DAX function 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.

It’s comes under Time Intelligence DAX function category.

Syntax:

SAMEPERIODLASTYEAR()

Parameter:

dates : A column containing dates.

Note:

The dates argument can be any of the following:

A reference to a date/time column,
A table expression that returns a single column of date/time values,
A Boolean expression that defines a single-column table of date/time values.

This function does not support DirectQuery mode.

So, Let’s start with an example, download the sample Dataset from below link-

Global Super Store Dataset

Note: After load data into Power BI file, check the datatype for order date column, if datatype is not in date then change it as in date.

Follow these steps-

Step-1: Create a calendar table to using existing dataset “Order Date” column.

Go to Modeling Tab > Click to table icon & write below DAX

Calendar = 
CALENDAR(MIN('Global-Superstore'[Order Date]), 
MAX('Global-Superstore'[Order Date]))

Step-2: After that create a relationship between both table.

Relationship

Step-3: Now create a measure to get last year sale on same day.

SAMEPERIODLASTYEAR =
CALCULATE
(Sum('Global-Superstore'[Sales]),
SAMEPERIODLASTYEAR('Calendar'[Date])
)

Step-4: Add table visual into report page and drag Date Column from Calendar table, Sales from Global-Superstore table and new created measure.

Table visual with columns

You can see in below screen shot, it returns one year back sale on same day if exist.

Sampleperiodlastyear Example

Add year column instead of complete date-

Now it returns last year sale against each year.

SAMPLEPERIODLASTYEAR DAX Example

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.

DAX SAMEPERIODLASTYEAR