- Ciao friends.
In this video, I want to analyze with you
the features of automatic date-time
and automatic time intelligence in Power BI.
As you're gonna see there are pros and cons.
Actually there are more cons than pros
but... let's go deeper
and let's analyze what's happening there.
Now first of all, we need to understand,
what is this feature?
Look at this model.
I have a report here
where I'm just slicing by year, quarter
and month sales amount.
The model is utterly simple, it only contains a sales table.
We have sales, customers
and then another table purchases
but let's focus only on sales.
If we look at the content of sales,
you will see that there are no information about
dates in sales.
Sales only contains the order date,
quantity and the net price and a bunch of other columns,
but there is no information about the year,
the quarter and the month.
So how is it possible that I can do an analysis
where I slice my report by year, quarter and month.
Actually Power BI has a feature
that automatically creates date table for us
if we do not disable it
and if there are no date table in the model.
How does it work?
It basically scans the entire model searching
for columns in the model that are of type date time
and it generates these tables for us.
Then these tables are hidden, you cannot see them.
You cannot interact with them,
but they give the feature of slicing in a simple way.
There are some advantages
and some disadvantages.
Let's start with the advantages.
Once you have a date table created by automatic date-time,
you can also create automatic time intelligence.
So let's pretend that I want to see sales amount
and I also want to create the year-to-date of sales amount.
I don't know that's enough to create a measure
but there is a quick measure.
So what I can do is simply go to my measure, sales amount,
right-click and create a new quick measure.
When you create a new quick measure,
you have a set of different options.
I'm interested in the year-to-date.
And for the date, I say I want the year-to-date
based on the order date.
Clicking OK is enough to let the engine create
such a measure for me.
Then I can take my sales amount
year-to-date measure, put it in the report
and let's start from the beginning.
It works, it is computing
a year-to-date in an easy way.
But if we go a bit deeper
and we look at the content of this measure,
you will see that, there is a strange syntax
IF ISFILTERED Sales order date,
then it calls TOTALYTD, sales amount
and this syntax sales, order date.date
is kinda weird.
We are not used to adding a dot
and then another column name at the end
of column specification in DAX.
This is an extension column.
This is a syntax that Power BI only uses
when you have tables created with auto date-time.
You can use it, but it's not the best practice
because as we are going to see it's not
the best practice that of letting Power BI
creating these tables for you.
So the good of automatic date-time is
that it is automatic, you don't have to
worry about it and you have the capability
of using time intelligence functions.
With that said there are also some drawbacks.
The first one is that the Power BI engine will
create one table per each date in the model .
You have two date columns.
There will be two date tables, a hundred date columns
that means a hundred date tables.
This doesn't seem a big problem.
But it means that each separate date table will we be able
to filter only one column.
You might remember in this model,
we had multiple tables.
We had sales and we also had purchases.
What happens if I want to see in the same report
the sales amount and the purchase amount?
The problem I'm gonna face is that these,
these values here in the rows,
they only filter the sales order date.
The filter will not go from these columns
to the purchases table.
Indeed if I expand this a lot
and from purchases,
I take the purchase amount
and I add it to my report,
you see that it shows the grand total everywhere.
Why that?
Because the filter from the year
will not reach the purchase amount.
So having multiple dates which are not linked
to the same date table is an issue
and the problem is not only
if you have multiple tables.
Also if you have only one table containing sales,
you might face a similar scenario.
Let's pretend that I don't want to have purchase amount,
I only have one table containing sales,
but I have two dates.
So I have in my model both this side,
the order date and the delivery date
so I might want to produce the delivery amount
and the order amount together
on the same report.
Again, if I just rely on automatic date-time,
I am not going to be able to succeed in this report
because the date either filters the order date
or the delivery date.
So what can you do if you want to solve those problems?
Well, what you need to do is spend a bit more time
and create an automatic date table.
A date table not in an automatic way
but created by yourself,
but this is not a long process.
It takes just a few minutes,
but that's a topic for another video.
Enjoy DAX!