When you use the time intelligence functions in DAX, it is relatively easy to filter the same dates selection in the previous year by using the SAMEPERIODLASTYEAR or DATEADD functions. However, if you follow the best practices, it is likely that you have a full date table for the current year, which includes many days in the future. If you are in the middle of March 2017, you have sales data until March 15, 2017, so you might want to compare such a month with the same number of days in 2016. And the same when you compare the Q1, or the entire year.
A common solution is to translate this request in a month-to-date (MTD) or quarter-to-date (QTD) comparison, but depending on how you implement this, you might not obtain a reliable result. For example, you might assume that the current date on your PC is the boundary of the dates you want to consider, but you probably have a few hours if not days of latency in the data in your database, so you should constantly fix the offset between the current day and the last day available in your data.
Thus, why not simply relying on the data you have to make an automatic decision? This is the purpose of the technique described in the article Compare equivalent periods in DAX that I wrote on SQLBI, where I show several approaches optimized for Power BI, Excel, and SSAS Tabular, which are different depending on the version you use.
Personally, the version I prefer is the one with the variables in DAX:
[PY Last Day Selection] := VAR LastDaySelection = LASTNONBLANK ( 'Date'[Date], [Sales Amount] ) VAR CurrentRange = DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), LastDaySelection ) VAR PreviousRange = SAMEPERIODLASTYEAR ( CurrentRange ) RETURN IF ( LastDaySelection > MIN ( 'Date'[Date] ), CALCULATE ( [Sales Amount], PreviousRange ) ) It’s longer, but much more readable. More details and examples you can download for Excel and Power BI are available in the article.
Returns a set of dates in the current selection from the previous year.
SAMEPERIODLASTYEAR ( <Dates> )
Moves the given set of dates by a specified interval.
DATEADD ( <Dates>, <NumberOfIntervals>, <Interval> )
Returns the last value in the column for which the expression has a non blank value.
LASTNONBLANK ( <ColumnName>, <Expression> )
Returns the dates between two given dates.
DATESBETWEEN ( <Dates>, <StartDate>, <EndDate> )
Returns the smallest value in a column, or the smaller value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.
MIN ( <ColumnNameOrScalar1> [, <Scalar2>] )
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
IF ( <LogicalTest>, <ResultIfTrue> [, <ResultIfFalse>] )
Evaluates an expression in a context modified by filters.
CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )