- Ciao friends.
In this video, I want to show you how
time intelligence function work with the filter context.
And to do that, I'm gonna create a measure
that computes the year-to-date over time.
And I will filter it using the weekdays.
As you will see,
this is gonna create some strange behavior.
We will need to understand and then fix it.
Let's start from the beginning.
I already created a calculation
that computes the year-to-date
and you can see it here.
Now before diving deeper, let's look at the code.
There is nothing strange about this code.
It's just CALCULATE sales amount
and then we use dates YTD
to create a YTD filter on the date table.
And as it is now, the measure is working totally fine.
I already have a report
that is showing day by day.
You see that the sales amount year-to-date
simply grows from the beginning
and it will continue growing up to the end of the year.
The problem appears if I activate the slicer on the weekday.
What if I'm interested in computing
a year-to-date of only Tuesdays?
I can do that.
I can filter Tuesday here
and look at what happens on the code.
This is the sales amount.
This is the sales amount year-to-date.
It's no longer growing as I would expect it to.
Indeed, it starts with 2.5 then 20, 38, 56
even though the individual values of sales amount
are much much smaller.
The first thing that we need to understand
is what is this number?
What is the engine computing?
Once we will have understood it
then fixing it is much simpler.
In order to understand what is that number,
I created another report
which is not affected by this slicer.
And again, I'm showing the values day by day.
Now let's start from the beginning.
On the 8th of January, we have 20 millions.
Where are my 20 millions in the regular year-to-date?
If we look at it, this is the result of a regular YTD
on the 8th of January.
That 20 million is the same number
you have seen in the other place.
So my measure is not computing the year-to-date
for only Tuesday.
It's just computing a year-to-date
completely ignoring this filter.
Then it is showing only Tuesday,
but the calculation happens on the entire period.
Why is this happening?
That's a specific behavior of time intelligence functions.
Let's look back at how my measure is defined.
My sales amount year-to-date applies a filter
using dates YTD over date.
Now dates YTD returns a table
containing all the dates
from the first of January to the last visible date
in the current filter context.
And that filter will work
on the date column in the date table.
If your date table is marked as a date table,
or the relationship that links it to the sales table
is a date time column,
then the DAX engine will automatically add
an ALL to your calculation
in order to get rid of all the filters.
This behavior that looks kind of strange
is in reality correct and expected,
because it makes all the time intelligence calculations
much simpler to author.
But in cases like this one
where we want to keep some filters coming from slicers
or other graphical entities,
this behavior is breaking our code.
Since we cannot disable this behavior,
what we need to do is to recreate the filter
that we want to keep active.
Now, we have a filter on the weekday.
We know that dates YTD will kill among all the filters,
also the filter on the weekday.
What we need to do is restore the filter on the weekday.
So in order to fix the code,
what we need to do is let dates YTD do its job.
So dates YTD will remove the filter,
and we will use the VALUES function
to restore the filter on the date weekday.
As soon as I do this modification,
you see that my code now works as expected.
Sales amount has 2.5, 2.4
and sales amount YTD is 2.5, 4.9, 7.6.
It grows exactly as expected.
Of course, this measure works for this specific report
because in the code what you need to do
is to declare the filters that you want to keep.
So it's very hard to create a calculation
that will work for any report.
Likely it is not so common to have the requirement
to compute a year-to-date, for only specific days,
but if that is one of the requirements you have,
now you know how to solve the problem.
Enjoy DAX!