Table of Contents
Introduction – How to get the Same Period Last Year to Date in Power BI
Microsoft Power BI has an inbuilt Time Intelligence function, SAMEPERIODLASTYEAR, to return 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. However, this function may have certain limitations and may not always return the precise date range in some situations.
In this article, we will discuss the limitation of SAMEPERIODLASTYEAR and find the solution so that this Time Intelligence function can return the date range accurately to calculate the performance metrics.
Understand the SAMEPERIODLASTYEAR function first.
SAMEPERIODLASTYEAR
This DAX function returns exactly what its name says, same Period but for last year. It returns a one-column table containing dates shifted one year back in time from the dates in the current context.
Syntax:
SAMEPERIODLASTYEAR(<dates>)
Parameters
dates – A column containing dates.
The dates argument in SAMEPERIODLASTYEAR can be any of the following:
- A reference to a date/time column from the Date table,
- A table expression in DAX that returns a single column of date/time values,
- A Boolean expression of DAX that defines a single-column table of date/time values.
Return value
A single-column table of date values same period but for last year.
Note: The single-column table of date values are same as the dates returned by this equivalent formula: DATEADD (dates, -1, year)
What is Same Period Last Year?
Let’s understand the same PERIOD last year with an example. It will help us in identifying the limitations of the SAMEPERIODLASTYEAR function.
Suppose we have a sales table with data starting from 1 Jan 2021 to 15 Aug 2022 and have a separate calendar table with Date, Month, and Year columns fulfilling all the criteria of the Date Table in Power BI/Power Pivot. Also, both tables are in one-to-many relationships.
Filter scenarios and output:
- If we apply a filter on the day level and select 12 August 2022 then, SAMEPERIORLASTYEAR would return the same day for last year i.e., 12 August 2021 as date output. If our selection is a date range, from 5 August 2022 to 12 August 2022 then, this function will return the date range from 5 August 2021 to 12 August 2021.
- If we are slicing or dicing the data at the month level and selecting August 2022 in the month filter then, this function would return the same month last year with a complete date range for August i.e., 1 August 2021 to 31 August 2021. The same behavior will be applied to the Quarter filter as well.
- If the filter has been applied on Year with 2022 in Year filter without having any additional filter on Month and Day then, this function would return the same period last year to month date range i.e., 1 January 2021 to 31 August 2021.
Note: If the multi-column filter is applied to Date Table, then the filter logic sequence would be 1. Date, 2. Month, 3. Quarter and 4. Year basis on filter availability.
Here, if you notice that in both the Month and Year filter scenarios, the SAMEPERIODLASTYEAR function returns the full date for the available months regardless of the partial date range available in the data.
This behavior always creates confusion for users as whenever we apply a filter on a Month, then we expect that the SAMEPERIODLASTYEAR function will return the same period MTD last year not the full month data if the current selected month has a partial date range.
In the above scenario, we have data till 15 Aug 2022 for August 2022, but SAMEPERIODLASTYEAR will return the complete date of August 2021 i.e., 1August 2022 to 31 August 2022.
The same issue is applicable for Year as well as it doesn’t return the same period last year to date range but returns last year to month date range.
Why is SAMEPERIODLASTYEAR behaving like this?
SAMEPERIODLASTYEAR does not understand the Cross filter applied on the Date column through the Month and Year columns filter or any other column data model. This function understands the direct filter on the Date column and ignores the Cross Filter coming from another column of the Calendar table. Due to this reason, it does not consider the partial date range for a month and takes the full date.
How can we get the same period last year to date range using SAMEPERIODLASTYEAR?
To get the same period last year to the date, we need to perform some checks and then, apply a filter on the date column while passing it to the SAMEPERIODLASTYEAR function in some scenarios.
- First of all, we need to check whether the direct filter is applied to the Date column or not. If there is a direct filter available on the Date column, then we don’t need to do anything. We can simply pass the Date column as an argument in SAMEPERIODLASTYEAR.
SAMEPERIODLASTYEAR (‘Calendar'[Date])
Note: In DAX, we have two functions to check the filter type on the column:
ISFILTERED(<columnName>) – This returns a TRUE value when a direct filter is applied to the columnName parameter in Power BI. If there is no direct filter on the columnName, the function will return FALSE.
ISCROSSFILTERED (<columnName>) – This returns a TRUE when a filter applied to another column in the same table or in a related table affects columnName by filtering it.
- We need to find the max date in current filter context and match whether max date is equal to end of month date (EOM) or not. If max date and end of EOM both are the same date, then we don’t need to filter the Date column and pass the date column as it is in SAMEPERIODLASTYEAR.
This check will help us in incorporating the leap year issue in the function.
SAMEPERIODLASTYEAR (‘Calendar'[Date])
Note: To get the max date, you can use the Max function and the for End of Month date, you can use EOM.
- If there is no direct filter on the date column or the max date is not the end of the month date then we need to apply a filter on the date column and pass the filtered date to the SAMEPERIODLASTYEAR function.
Here, the filtered date column should not be equal to without filter date as SAMEPERIODLASTYEAR ignores the filter if both the ranges are the same. For example, if we are filtering the date with <=Max(‘Calendar'[Date]) then the output of the filtered date is the same as without the filter date then in that scenario, the SAMEPERIODLASTYEAR function will assume that there is no direct filter on Date column and it will go with its default behavior that is taking the full date of the month despite partial date in the data model.
To handle this situation, we will go with two SAMEPERIODLASTYEAR functions- the first would be with <Max(‘Calendar'[Date]), and the second would be with =Max(‘Calendar'[Date]). This is how we will generate two date ranges with SAMEPERIODLASTYEAR and later on we will use the UNION function to merge both the range and make them one.
Let’s move to Power BI and perform the required validation and changes in SAMEPERIODLASTYEAR function to return year-to-date range as output.
In Power BI, we have two tables: Sales, and Calendar in Power BI.
We have created four different measures 1. Total Sales Amount, 2. Total Quantity, 3. SPLY Sales Amount, and 4. SPLY Quantity
Total Sales Amount: Calculate the total sales amount
Total Sales Amount = Sum(Sales[Sales Amount])
Total Quantity: Calculate the total quantity
Total Quantity = SUM(Sales[Quantity])
SPLY Sales Amount: Calculate the same period last year Sales Amount
SPLY Sales Amount =
CALCULATE(
[Total Sales Amount],
SAMEPERIODLASTYEAR(‘Calendar'[Date])
)
SPLY Quantity: Calculate the same period last year Quantity
SPLY Quantity =
CALCULATE (
[Total Quantity],
SAMEPERIODLASTYEAR ( ‘Calendar'[Date] )
)
SPLY Sales Amount and SPLY Quantity both are based on standard calculation methods of SAMEPERIODLASTYEAR.
In below formula for same period last year Quantity, we are performing all the checks and changes in function to get the output year-to-date for SAMEPERIODLASTYEAR.
SPLY Quantity (Year-to-date) =
VAR MaxDate =
MAX ( ‘Calendar'[Date] )
VAR FilterCheck =
ISFILTERED ( ‘Calendar'[Date] )
VAR EndDateCheck =
EOMONTH ( MaxDate, 0 ) = MaxDate
VAR SPLYDates =
UNION (
SAMEPERIODLASTYEAR (
FILTER ( VALUES ( ‘Calendar'[Date] ), ‘Calendar'[Date] < MaxDate )
),
SAMEPERIODLASTYEAR (
FILTER ( VALUES ( ‘Calendar'[Date] ), ‘Calendar'[Date] = MaxDate )
)
)
RETURN
IF (
OR ( FilterCheck = TRUE (), EndDateCheck = TRUE () ),
CALCULATE ( [Total Quantity], SAMEPERIODLASTYEAR ( ‘Calendar'[Date] ) ),
CALCULATE ( [Total Quantity], SPLYDates )
)
This is how we can get the exact same period last year to date. This logic is also applied to the DATEADD function to get the date till the month, quarter, and year.
Please leave your comment and feedback. Thanks!
Download the Power BI file from the below link.
If you have any questions about this tutorial, please leave a comment and TheDataLabs team will respond to you as soon as possible.
