- Ciao friends.
In this video, I want to show you how to compute
the sales in the previously selected month.
This is the report that I want to create with you.
It shows the sales amount
and the sales in the previous month.
But this is not really the previous month,
that would be simple to compute with a simple DATEADD.
This is the previously selected month.
In order to understand it better, let's look at the detail.
You see that using the slicer,
we selected some months out of the entire selection.
That means that we are not interested in April,
June, July and September.
So when I speak about the previously selected month,
for October, that need to be August.
And for August, it will be May.
And for May, I want to retrieve March.
So I don't want the previous month,
I want the previously selected one.
And this is what the measure computes.
You see that the sales in the previous month for October
is showing the sales of August.
And for May, it is showing the sales of March.
The code is a bit more tricky
than a simple PREVIOUSMONTH or DATEADD.
We will need to work with the filter context
and pay attention to several details.
Let's do that together.
In this report, I already have the sales previous month
that is working.
Sorry, in this report,
I already have the sales in the previous month
and it's working just fine.
But what I want to do, as we always do,
is write the code step by step
so we understand all the details
and the problems that we might face.
First of all, I need to find a way...
when I'm in May,
to retrieve the value of March.
To do that, what I need is a column
that contains the year and month,
together in the same column.
And it happens that I already
have such a column in my model.
Let me zoom in a bit.
You see that in the date table,
I have the year month number
that contains 200501 for January
and then 200502, 03, 04 for all the different months.
So this column uniquely identifies the year
and the month together.
And in order to retrieve my calculation,
what I need to do is take the filter context, which is May,
and retrieve the maximum year month
that happens to be before the current year month.
Let's do that together, step by step.
So, I first build a new measure
and let's call it new previous month.
The first value that I need to retrieve
is the value of the current year month.
So, in a variable, I store the current year month
and I use SELECTEDVALUE in order to retrieve
SELECTEDVALUE in order to retrieve the value
of the date year month number.
In the RETURN part,
let's start by returning the current year month.
So we see the partial results
in the process of writing the code.
Let me add new PM here,
go a bit down and you see that in March 2008,
it retrieves 200803, which is the current value
for the year month.
Now I need to retrieve the previous one,
so when I'm here in 200805, I want to retrieve 200803.
In order to do that, I need to find the maximum value
of the column year month, not in the current filter context,
I need to enlarge the filter context to see everything
and retrieve the maximum one.
So I can create a new variable, previous year month
and I use CALCULATE to retrieve the maximum value
of date
date year month number
where are you? Here.
And I need to get rid of the filter context.
So I need to use ALL on date,
so to see everything which is outside.
And I also need the year month number,
year
month number to be less than the current year month number.
I lost my cursor, here it is.
Let's see what we have in previous year month now.
Previous year month.
Again, we need a bit more space
and also reduce the font a bit.
It's not working.
You see that in May, it does not return March,
it returns April because we used this ALL
that entirely removed the filter context.
We do not want to remove the entire filter context.
We want to keep the filter coming from the slicer
and remove any other filter.
So we want to remove the filter from the current visual
and we want to keep the filter from outside of it.
So I can use ALLSELECTED instead of ALL.
ALLSELECTED will remove the filter from the current visual,
but keep the filter from the slicer.
And now you see, we are one step farther.
Now it retrieves 200803.
So March 05, that is May
and then it retrieves August in October.
So that's a good step forward.
It is not yet perfect, why that?
Well, we need to pay attention
to the fact that this date year month number,
with less than current year month,
removes and replaces any filter
on the current year month number.
So in this report right now, everything is working fine,
but what happens if the slicer
instead of filtering another column,
like in this case the month name,
it is filtering exactly the column year month number?
I have prepared already a report here
that shows the problem.
The slicer is now filtering the year month number
instead of filtering the month name.
And if I take my code, new PM, and I put it here,
you see that it does not work.
For 200803 it returns two,
for five, it returns four
and for seven, it returns six.
Why that? The reason is that
the filter... let's go to the code,
this date year month number,
replaces the filter from the slicer.
The slicer filters the year month number.
My condition is filtering the year month number,
so even though I have ALLSELECTED here,
this date year month number
removes the filter from the slicer.
That is why we need to use KEEPFILTERS here,
to make sure that we do not kill the filter
on the year month number, if present.
And if I look at the code now,
you see that for May, it returns March
and for seven, it returns five, which is here.
The selection here is not exactly the same
that I had in the previous report,
but you got the point.
So this KEEPFILTERS is not always needed,
but it is needed in the very special case
where the slicer is filtering the same column
on which you are doing your work,
which is unlikely to happen with the date,
but might happen with different columns.
Now I have my previous year month
and now it's easy.
All I need to do is compute the result,
which uses CALCULATE.
We want the sales amount
and we want the date year month number
to be equal to the previous year month.
That is not enough because that replaces the filter
on the year month number,
but we also want to get rid of any other filter
from the date
because our new filter, year month number,
need to replace whatever filter is active at that point.
And if I hit Enter now, I need to RETURN result.
Okay.
And we have the code now.
We only need to format it the right way,
it's not a whole number, it's a decimal number
with two decimal places and commas.
Here we go.
Now you see that our new previous month
computes exactly the same value
as the sales in the previous month.
In order to obtain the result, we need to pay attention
to several details.
First this ALLSELECTED to restore the filter on the date but
KEEPFILTERS in order to replace the filter on the year month
keeping the filter from the slicer, if any.
And finally this ALL date in order to kill
any filter present on the date because
we are about to replace it with the year month number.
As you have seen,
computing these calculations is not that hard,
even though you need to pay attention
to combine the filter context the right way,
using ALL, ALLSELECTED and conditions
to obtain exactly the number you want.
If you are interested in this topic,
make sure to subscribe to this channel
and most important, go to SQLBI.com
there you will find not only this article
with the downloadable file,
but also a lot of article and content that will help you
in learning DAX the right way.
Enjoy DAX.