- DAX offers you two functions to take a selection of dates
and shift it back and forth over time.
They are DATEADD and SAMEPERIODLASTYEAR.
SAMEPERIODLASTYEAR is a simple function
whose only goal is to shift the time back one year.
DATEADD is a more generic function where you can specify
both the amount of the shifting and its unit of measure.
Let's see them with an example.
In this first example, I have a selection of dates
that starts from the 15th of July 2008,
up to the 31st of July 2008.
And I use SAMEPERIODLASTYEAR to retrieve
the same period one year back.
So the CALCULATETABLE applies a filter
between the start date and end date
and SAMEPERIODLASTYEAR returns a table
with the same selection one year back.
You see that the result goes from the 15th of July 2007.
So one year back compared with 2008,
and goes back to the 31 of July 2007.
SAMEPERIODLASTYEAR is very frequently used,
but it has the limitation
that can only shift time back one year.
If you need more flexibility, then you need to use DATEADD.
Using DATEADD, you specify, you use DATEADD
and you specify both the amount of the shifting
and also the unit of measure.
Using DATEADD minus one year
results in the same as, SAMEPERIODLASTYEAR.
So if I have a selection of dates
from the first of January 2008
up to the 31st of January, and I use minus one year,
I run the query and I obtain all the dates between
the first of January 2007 up to the 31st of January 2007.
The main advantage of using DATEADD is that you can change
the selection and go back for example one quarter
starting from the first of January 2008, and going back
one quarter I obtain the first of October 2007,
that is the same period shifted back one quarter.
DATEADD has quite a complex logic that lets it choose,
lets it detect when the current selection
includes a full period.
Look at this example, I have a selection
where I start from the first of February 2008,
up to the 29th of February 2008.
So the selection contains 29 days,
but that selection happens to be an entire month.
And if I use DATEADD to shift this period
ahead one month, the new selection will contain
from the first of March 2008, up to the 31st of March 2008.
DATEADD detected that the selection contained
the full month of February and instead of returning
29 days of March return the full period.
So it has a selection which is larger than the original one.
The same happens the other way around.
If I select from the first of March
to the 31st of March, and I go back one month,
now I start from a selection with 31 days,
but going back one month,
because February does not have 31 days,
it will produce a result containing only 29 days.
So the entire month of March.
The logic is quite complex,
and we have written it in the DAX guide website.
It's not worth going into all the details.
What really matters is that it usually works fine.
So when you have a selection of a period, either a month
or a quarter, you obtain the same period in a previous
timeframe, but containing the same overall logic.
An important detail about all these functions
is that they are table functions, their result is a table
like any other time intelligence function.
The reason is, you will typically use this function
as an argument of CALCULATE, so you write CALCULATE,
sales amount or whatever measure
and you use SAMEPERIODLASTYEAR
to shift time back of the period.
Then I use this expression inside
another CALCULATE that applies the filter context.
You typically do not have the outer CALCULATE
because that is the filter created by Power BI
or whatever client tool,
to force your calculation to work in a specific period.
The result of SAMEPERIODLASTYEAR is a table
containing all the dates from the first of January 2007,
up to the 31st of January 2007.
And this is a table that CALCULATE uses
to modify the filter context and perform the calculation
under a different filter context.
The result are the sales in the period
that has been shifted by SAMEPERIODLASTYEAR.
Another important detail
is about the syntax of these functions.
You write SAMEPERIODLASTYEAR date date,
but actually the arguments of SAMEPERIODLASTYEAR is,
the argument is not a reference to a column
but it's a table.
Date date is nothing but syntax sugar
for the more complex expression
that is SAMEPERIODLASTYEAR, CALCULATETABLE,
DISTINCT date date.
So DISTINCT retrieves a table,
CALCULATETABLE performs context transition.
This is an important detail,
because when you use DATEADD or SAMEPERIODLASTYEAR,
context transition is happening.
Therefore, running these functions in a row context
might trigger context transition,
and you need to be aware of that,
in order to understand the results which are coming out.
As a last detail, let's see these functions in action.
I have a query that retrieves the calendar year month,
the sales amount in the calendar year month,
and then I use CALCULATE to go back one month,
one quarter or one year with SAMEPERIODLASTYEAR.
Let's look at the result.
January 2007, has 794 as the value,
in February sales amount shows the value of February,
but the same period last month going back
one month return still the value of January.
The value of the last quarter is blank at the beginning
because there is nothing in the previous quarter.
But when you are in April,
you see that in April it shows 794
which is the same one month but in the previous quarter.
Similarly for the year it is blank at the beginning
and it starts to show 794
that is the value of January 2007,
when we are in January 2008.
You can use this function to shift time back and forth
and to compute many time intelligence calculations.