SAMEPERIODLASTYEAR is a Power BI time intelligence function in DAX which returns a table that contains a column of dates in the current selection of previous year.
It is specific to a year interval and returns the same as the dates returned by this equivalent expression:
DATEADD(dates, -1, year).
Lets look at an example of SAMEPERIODLASTYEAR function in Power BI.
To demonstrate this, lets take a sample datasets. As you can see below tables as SalesData which contains a Sales Amount by Sales Date and a Dates table which contains dates.
There is a relationship exists between these tables on columns SalesDate and Date column as shown below.
Following example, displays the Total sales by Year.
TotalSales = SUM(SalesData[Amount])
As you can see, it returns the Total Sales by Year.
Using SAMEPERIODLASTYEAR Function
Lets see the Total sales for the previous years values using SAMEPERIODLASTYEAR function.
Following measure uses the SAMEPERIODLASTYEAR function which returns the Total Sales in the current selection of previous year.
Once you commit the measure, lets drag it into table visual next to Total Sales filed to see the output.
As you can see, it returns the Total Sales of last year for same period, means if you are looking at data on the year level then SAMEPERIODLASTYEAR function returns the Total Sales for same period but last year.
Lets see what happens, if you change the data view at month level.
For this you no need to make any changes or specify month field to SAMEPRERIOLASTYEAR function, Just drag the month date field into table visual right after the Year field as shown below.
As you can see, this time DAX change it’s behaviour and returns the TOTAL Sales for same month but last year.
Similarly, if you see the data at Quarter level, it would be same period but last year.
Lets look at quarter level, just remove the month and add quarter date field into table visual as shown below.
You can see, this time SAMEPERIODLASTYEAR function returns the Total Sales for Quarter but last year.
Again, if you want to see the data at day level, it would return the total sales for same day last year.
So using this function, you can compare the values for same period but last year based on that how you want to see the data, if you are looking the data at Year level, it would return same period but last year, similarly for month, day, and quarter level.
The good thing is that for this you no need to specify any date part such as year, month, quarter in this function. You just need to specify date field only.
Table Manipulation DAX
4,489 total views, 15 views today