- Ciao, friends. In this video, I want to show you how to create a simple date
table just using Power BI. Let's start with the model. I have a simple model, here, that only contains sales and customers. There are no dates anywhere, and I have disabled the
auto date time feature. Therefore, if I take the order date and I drop it in my report,
I'm just slicing day by day. And this is not the optimal choice. What I want to do is
being able to slice by year, quarter, month, and
to create my own date table. To do that, I need to create
a new calculated table and just write some DAX in order to customize it the right way. So, let's start from the beginning. We go here and we create a new table. We just name the table "date" and there is a function in DAX, which is named CALENDARAUTO,
that scans your model, all the date columns in your model, and builds a suitable date table that will hit all the years which are referenced by your dates. So, we can just use
CALENDARAUTO and, as you see, the result contains a new column, "date", that starts from 2008 and will end on the last day of the last year referenced in your sales table. Or, for instance, in any
date column in your model. As it is now, it only contains a date, so it's not really useful. What we need to do is to
enrich it with more code. We could create columns just by creating new calculated column using the UI, but we prefer to use DAX, so we write everything
in a single expression that we can copy and
paste wherever we need. There is a function in DAX, ADDCOLUMNS, that lets you create new
columns and add them to a table. Now, CALENDARAUTO returns a date. What if I want a new
column containing the year? Then, I need to provide, first of all the name of the column, and then, the expression
which will be evaluated in order to return the year. This is the first column, and you see we have a new
column containing the year. You need to proceed and
create, in this way, all the columns that you
need in your date table. For example, we need the year, we also need the month, so we use the format function, and we format the date as a month, and we also need the month number because we will want to sort
the months the right way. It's just seven lines and we already have the date, the year, the
month, and the month number. What if we want the quarter? Well, we can also add the quarter again using the FORMAT function and we format the date as slash quarter. Now, I already have a good date table that contains the year, the month, the month number, and the quarter and I can use it to slice the data. The thing is, I still need
to create a relationship. Now, in sales, I have my order date. I link it to the date
column in the date table. Now, I have the relationship set and I can remove the order date beside, not only remove. It's a good practice, that
of hiding this column, so that... you have no way of using it, at least, if you're a casual user, and then, I can start slicing by year. Now, as you can see, I'm slicing
by year, the sales amount. If I try to do that by month, too, you will see that the
result is not optimal. In fact, it shows April, August, December. The reason is, it is using
alphabetical sorting. What you need to do is sort
the months by month number. And there's a feature to do that. So, you need to select the month, go to sort by column, and
choose the month number. As soon as I do it, you will see that the months
will be sorted the right way, January, February, March, and so on. That's the reason why we created both the month and the month number. The same way, you can use the quarter and you can enrich this table with all the columns you might want. For example, you can add the
weekday, the week number, you can add all the columns
that you might need. Create hierarchies and do all the work. If you need a simple date table, this is the way to go. If you need a much more
complex date table, or if you want to see a very good example of how to build the ultimate date table, there is a tool in Power
BI that you can look at, which is the DAX Date Template. This is the URL that you need
to enter in order to find it and, from here, you can
download a Power BI template that contains the ultimate date table. A lot of DAX code that
contains the definition of nearly all the kind of
columns that you might ever need. You can use it as it is or you can use it to customize
your perfect date table. It also contains hierarchies
and all the structure. That's the ultimate point. We are showing, here the very
simple version of the DAX code very simple version of a date table. The last thing that I
want to show you is that, sometimes, CALENDARAUTO
creates too many dates. CALENDARAUTO scans all the
date columns in your model, including, for instance, the birth date or whatever date might be there. So it might be the case
that it goes too wide. It creates a large date table. It's not a good practice that
of having a large date table. You only need the dates
that you actually need. The years that you actually need. So, I can just create
a variable "Mindate", which contains the minimum
of sales order date, then I create a second variable. Let's call it "Maxdate", which contains the MAX
of sales order date. Actually, I don't want
the first and last date because I want the first and last year. CALENDARAUTO will take care
of selecting individual dates, so let's just extract the YEAR of the MAX, and the YEAR of the MIN. And then, I'm gonna use these
variables in the RETURN part, and I embed my CALENDARAUTO
inside a FILTER and I filter CALENDARAUTO where the YEAR of the date
is greater or equal to the Mindate and at the same time, the YEAR of the date is
less or equal to Maxdate. Okay. This code is not going to have any effect on this very simple model. But on a more complex model, or in a different data structure, this is extremely useful to restrict the days
to only what you need, and beside, your logic might
be even more complex than that. You might have different dates, you might have dates in the future, you might have any scenario. This, as a pattern, lets you choose the only
dates which are needed. The last touch, in order to
make it a perfect date table, is to mark it as a date table. If we select the date in the table tools, we have the mark as date table. You only need to check it
and to provide to the engine, an information about the column that contains a date in your table. A date table, to be a correct date table, needs to have one column of type date time containing the dates, and that column needs to be
unique through the entire table. By using mark as date table, you make sure that all time
intelligence calculation will work in a smoother way. You don't have to worry
about filter context, applying ALL, and doing
a lot of geeky stuff that Power BI, DAX, takes care of you, if you mark the table as a date table. Enjoy DAX!
