- Ciao, friends.
In this video, I want to show you
how to compute the sales in the previous year
up to a certain date in the current year.
Typically, what you want to compute are the sales
in the previous year up to the last date
for which you have sales in the current year.
It only requires one calculated column
and one simple modification to a measure.
So, let's do that together.
I have this reports that is showing 2009
and then the months up to September, and the sales amount.
It is showing values up to September
because our last date with sales
is the 5th of September, 2009.
I also created a measure previous year that uses CALCULATE
to move the filter context back one year
by using SAMEPERIODLASTYEAR.
This formula works just fine.
The problem is that if I put it in my report,
it shows data also for October, November, and December.
Indeed, there are values in October,
November, and December, 2008,
but I don't want to show those values
because they are in days which are in the future for 2009.
Not only I want to hide the values,
I also want to avoid performing the calculation.
If you look at the grand total,
93 million is the grand total of the entire 2008.
I don't want that number.
I want to compute 2008, up to the 5th of September.
In order to solve the problem,
we need first of all, to store somewhere the information
about what dates are in the future.
So, we know that the 5th of September, 2009
is the last date,
but we need to store this information somewhere,
and then we will need to change that CALCULATE
so that it takes into account this new piece of information.
Let's do that.
First, we need a place where to store the information
about which dates are past
and which dates are in the future.
We know that the 5th of September, 2009
is the last date in 2009, but we want to mark dates
before the 5th of September, 2008 as past,
and after the 5th of September, 2008 as future.
To do that, we can create a calculated column
in the date table.
Here, my date table is very simple,
it only contains date, year, month, and month number.
I'm going to create a new calculated column
that tells me which dates are past and which are future.
So, we create a new column.
Increase the font.
Let's call it is past, and that column will contain true if
the date is before the 5th of September... 2008.
So, first we need a variable
which contains the date for which we have sales.
And this is just the MAX of sales order date.
Then we need to move this date back one year, or 12 months.
So, last date, last sale date
in the previous year, and we use the EDATE function,
that takes the last sale date, and goes back 12 months.
And finally, what is our result?
Well, if the current date happens to be earlier
than the last sale date in the previous year,
then the result will be true,
the date is past, otherwise it's false.
So we just check that the date is less or equal
than the last sale date in the previous year.
Now you see, for dates in January, 2008, this value is true.
If we go, not in 2009,
but in 2008 in September,
okay, here we are.
The 6th of September, 2008 is the first date
where this column is false.
Before, it's true, and after that, it is false.
Now that we have the column, we only need to use it.
We can go in our previous year,
let me keep previous year, I just copy the code,
and we create a new measure.
Let's call it previous year adjusted,
and adjusted.
No way, adjusted.
The code is the same, but we also require
that the date is in the past so we just add date,
is past, equals true.
In this way, I avoid looking at dates
where the is past flag is set to false.
Need to format it as a decimal number
and then drop the measure here.
Here we go.
Now you see that the previous year adjusted
shows the same values up to August.
Starting from September, the two numbers are different
and that is expected
because we have dates up to the 5th of September.
So any date earlier than the 5th of September
will not be considered, not in the grid,
but also at the total.
64 million is the value that I can compare
with the value of 64 million.
So, you see that by simply adding one calculated column
and a simple condition in your CALCULATE
you can obtain numbers that can be easily compared.
Enjoy DAX.