- DATESINPERIOD is a time intelligence function
that is useful to retrieve periods of dates,
starting from a reference date.
You need to specify as argument the column
that you want to retrieve,
a reference date, the size of the set you want
to retrieve and the unit of measure
that gives a meaning to the size.
Let's see that with an example.
In the first query, we use DATESINPERIOD.
We want dates starting from the 15 of August 2008
and we want the result to contain one day.
If I run the query,
you see that the result contains one day,
which is the 15 of August 2008
because it used the reference date
and the set needed to contain one day.
You can change the unit of measure
of the reference and if I run the query with one month,
then I have one month worth of data,
starting from the 15 of August 2008
and going down to the 14 September of 2008.
So month, day, quarter or year
is the unit of measure of the set
that you want to retrieve.
Whereas one, two, three
or whatever number you provide as the third argument
is the length of the set you want to retrieve.
If you use DATESINPERIOD, two, DAY,
it will retrieve two days,
starting from the 15 of August 2008.
You see the result contains the 15
and the 16 August 2008.
The reference date is always included in the set,
which is then extended to include the number
of periods that you requested.
If you use zero as the third argument,
then you are requesting a dataset containing zero period.
And as a result, you will always obtain an empty set
because no matter what you use as the unit of measure,
zero always contains nothing at all.
A very common usage of DATESINPERIOD
is to also go back in time.
You can use positive numbers
to go forward in time
but you can also use negative numbers
to go back in time.
DATESINPERIOD started from the 15 of August
and going back two days,
returns two days, the absolute value
of the third argument
but the sign of the third argument,
decides whether you want to go forward or backward in time.
So the result now contains the 15
and the 14 of August 2008.
Changing the unit of measure,
you can obtain the previous month.
DATESINPERIOD starting again from the 15 of August
and going back one month,
returns dates that starts from the 16 of July 2008
and go down to the 15 of August 2008.
Keep in mind, DATESINPERIOD does not return
the previous month.
So starting from the 15 of August,
it does not return the entire month of July.
It returns a month worth of data
that ends at the 15 of August.
If you want the previous month,
then you need to use different functions,
like PREVIOUSMONTH
that returns a full previous month regardless
of where the date is in the current month.
Let's now see a practical usage of DATESINPERIOD
to compute a moving annual total.
Moving annual average indeed.
The measure sales moving annual total first defines
a variable one year back
that always contains one year of data
and to compute it, we use DATESINPERIOD,
use the date as the reference date - sorry
date as the time of the column that I want to retrieve.
Then we use the reference date,
which is the MAX date date,
the last date which is visible in the current filter context
and we go back one year.
DATESINPERIOD will always contain a full year
of dates that ends with the last visible date.
We then use one year back
as a filter argument of CALCULATE
in order to perform the next calculation
in the full year of data.
And we compute the number of months,
the sales amount and if there are at least 12 months,
we compute the moving annual average.
You see the result for 2008.
It is showing the moving annual average
month by month where for every month,
it computes one year of data and divides the result by 12.