- Ciao friends.
In this video, I want to explain you the difference
between using time intelligence functions
and scalar functions.
The reason is, I see many people making a mistake
using time intelligence calculations
as if they were scalar functions, it's wrong.
And let me show you that with an example.
Imagine that in a report like this one,
I have the year, the month or the sales amount,
and I want to compute the number of days in the period.
So the number of days in January, February, March and so on.
Computing it is not hard,
you only need to subtract from the last day of the month,
the first day of the month.
The difference in days between the first and the last date
is the number of days in the period.
So a possible implementation is this one.
Let's say it's days in period is LASTDATE of date
minus FIRSTDATE of date.
And then I cast it to an integer
because I want a number as the result.
It works, and this is exactly the measure
that I'm pushing in here.
But the thing is, I'm using the wrong function.
Because what I want to obtain in this code
is the LASTDATE as a date,
I want the date which is the LASTDATE.
LASTDATE is not the same as the last date.
LASTDATE is a time intelligence function.
As a time intelligence function,
it is designed to return a table.
And the reason is,
LASTDATE as any time intelligence function
has been created to use as a filter in CALCULATE.
So you use LASTDATE in expressions like this, look.
SalesOnLastDate, this calculation returns the sales
for only the last date of the period.
It is using LASTDATE as a filter argument in CALCULATE.
This LASTDATE returns a table containing one date
that will act as a filter.
Now, let's double check it.
We can check the result of LASTDATE by using DAX Studio.
I already have a connection with DAX Studio here.
And, in DAX Studio you can run queries.
So we can run a query like EVALUATE date, for example.
'Date' in single quotes.
Because 'Date' is a table, it will return some result.
EVALUATE does not accept numbers,
it does not accept values as its result.
If you write EVALUATE one, this is not going to work.
And you see that it produces an error.
It says "The expression specified
"is not a valid table expression."
One is not a table.
If you want a table containing one,
then you need to enclose it in curly braces,
so that it returns a table containing only one value.
Now let's go back to LASTDATE,
if I EVALUATE LASTDATE of date
LASTDATE of date date,
the result comes out as December 31, 2011.
It's a table containing only one value.
So, LASTDATE does not return a number, it returns a table.
If you want to get a number, you should not use LASTDATE
you use MAX.
EVALUATE MAX does not work because it is not a table.
I need to enclose MAX inside curly braces
in order to transform it into a table
and see the same result as LASTDATE.
So keep that in mind, LASTDATE returns a date.
Let's go back to our previous code now.
Here we have days in period.
What is this code actually doing?
It's taking a table, LASTDATE date,
and from that table, it is subtracting another table.
So it's subtracting from one table, another table,
which doesn't make a lot of sense.
The thing is DAX, whenever you have a table
that contains only one row and one column,
can automatically convert this one row, one column table
into the scalar value, the number contained in that table.
That happens automatically, but it comes with a price.
Therefore, using LASTDATE or FIRSTDATE
to retrieve the minimum or the maximum values
of any date column is a waste of time.
A much better implementation is not to use LASTDATE
but to use MAX to retrieve the date,
and MIN to retrieve the minimum date.
MAX and MIN, they are not table function,
they will return the scalar value.
Nothing changes in the code,
everything is absolutely identical
to what we were doing earlier,
but this code is faster and better.
And beside that, if you're still making confusion
between...
LASTDATE and MAX or FIRSTDATE and MIN,
that means you still are not clear the difference
between a scalar function and a table function.
Therefore it's worth investing some time
in order to learn all these details.
Beside, there are also performance differences
between using LASTDATE or using MAX.
Generally speaking, whenever you want a scalar value,
MAX is much faster.
It does not perform context transition,
and it does not require the automatic conversion.
More details on this be found
on the SQLBI.com website where you can read the article,
download the web file and read more details about this.
Have fun with DAX.