Create Deponent Date with airSlate SignNow

Add simple date

- 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!