SAMEPERIODLASTYEAR Function in DAX / Power BI

SAMEPERIODLASTYEAR Function in DAX

 

SAMEPERIODLASTYEAR

This function is used to get the dates of the previous year with respect to the specified date column.

Syntax:

SAMEPERIODLASTYEAR(<DATE_Column>)

 

Parameter:

Date_Column – Any column which contains dates. It can be an expression which returns single column of date / datetime values. Or it can refer to any date or DateTime column.

Return value –

It returns a single column table with dates.

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

This function works same as DATEADD(Dates,-1,YEAR). SAMEPERIODLASTYEAR function cannot be used in DirectQuery mode.

This function is useful in time series analysis. It will be very useful when we have to compare measures of any time interval with that of corresponding previous year’s same time interval measures.

Example:

Following example will calculate last year’s sales

LY_Sales = CALCULATE(SUM(Orders[Sales]),SAMEPERIODLASTYEAR(DimDate[Date]))

 

CALCULATE is a function which Evaluates an expression in a context that is modified by the specified filters.

Let’s see how we can use in PowerBI

I am taking order table where I have Product Category, Region, Sales amount and Order date as some of the columns. Now, I would like to see Total Sales Amount of all months in the Selected year, previous year and also the difference between current year’s sales and Previous year’s sales for the selected product category, Region and Year.

In order to use SAMEPERIODLASTYEAR function, we have to have a table with contiguous dates. I am going to use DimDate for the same.

Steps:

1.       Create a measure for Total Sales Amount .

Total_Sales = sum(Orders[Sales])

2.       Create another measure for last year sales.

LY_Sales = CALCULATE(SUM(Orders[Sales]),SAMEPERIODLASTYEAR(DimDate[Date]))

3.       Create third measure for the difference between CY and LY Sales

LY_And_CY_Sales_Diff = [Total_Sales] -  [LY_Sales]

4.       Take Line and clustered column visual. Take Month on Shared axis, CY and LY as column values and Difference as Line Value.

5.       Take Year, Region and Category as slicers.

I have created 2 visuals one with Month as Shared axis in the first one and Quarter as Shared axis in the second one.

Appreciate Suggestions and Comments...