Interestingly what you're going to find is the method that looks the hardest is actually the best one to use let's get started if you are a power bi beginner or still feel like a beginner then this.
Channel is for you so make sure to subscribe and yes click that bell so you're notified whenever i answer your power bi questions let's start with the first method which.
Is the simplest but is it the best method well that will find out later but the first method is calendar auto date time in fact it is automatically built into power bi and uh.
It's active by default so all you have to do is to grab a date field and drag that over and you automatically going to get.
In let's look at it and if we can expand quarter one january february march everything is as it should be now again if we look.
At the underlying table the sales table we don't have that so it's not in the data we just had it simply a date field now i'm going to show you where this option lives so this lives in file.
Options and settings options and for the current data file data load there we go so current file data load you can see the option is right there and i do believe it's checked by default.
If you're not seeing this behavior go check this option make sure this box is checked however once you watch our other methods you might want to leave it unchecked so we'll see about that the.
Second method we're going to see to build a calendar table is using dax so for that we're going to go to the modeling tab and.
Posts Related:
Here we're going to say a new table so
Again a modeling tab new table and i'm click that and in the definition here i'm going to type in oops.I'm going to change the name you probably want to call it calendar but since i'm going to be creating multiple calendar tables i'm just going to call it calendar dax so i know which one it.
Is and here you have two really simple functions you have calendar and calendar auto i'm just going to first do the calendar auto and then i'm going to come back and show.
The calendar option to you now it has one uh parameter fiscal year and month but if you have a a year ending starting from january ending in december then you can just.
Specify like this hit enter and that's it and what it does is it creates that table we can go over to a really uh data view and look at that table so again this is.
The dax table that's been created and right now it just has this date field now of course what's interesting about the calendar auto is that it actually scans the other tables in your data.
Model and then figures out what is the date range and creates a table accordingly right so here you can see it starts with 1998 there must be some field in our data which has dates that.
Old and of course if we go all the way to the end it extends all the way to 2022. so let me show you the other dax function that.
I talked about which is the calendar field now if you notice here we are using our sales data and at least our sales data starts in 2019 january so maybe we can go back to our calendar.
Table here
And instead of calendar auto we can use the calendar function and calendar function oops just uses start date and end date so.Maybe we can have more control so start date i can say january 1 2019 since that's where my sales data starts and then.
12 31 uh 2022. now of course you can see uh the calendar auto would automatically.
Adjust to that but this one gives you a little more control before you think too much though wait till you see the third option and then we'll talk about what's this then well.
You will know what is the smartest and best way to handle all of this automatically so now we have this calendar table but it's really not doing much it's just has a date column i mean.
We can sure use that so i can go there connect this new calendar table to my order date there we go and here.
I can let me set this up all right so i'm ready to bring in this date column here from my calendar dax so i'm going to drag it over here now watch.
What it does it actually is still doing the auto date time but that's not what i want i want to create my own table so i'm going to change this option.
And say nope don't don't do that here don't use the hierarchy auto date time just use the simple date and then i'm going to go and create those columns there so for that let's go to our model.
Here and in here in table tools i'm going to say new column right so let's say new column and here i'm going to say year.
And calendar dax date right so give me the year there we go so we have the year in this column let's repeat that to get.
Quarter so here i can say quarter use that function and by the way you probably notice the best practices whenever you're referring.
Referring to column names that you use the table name prefix okay here and i want to prefix that with uh the the the letters qtr.
Let's see how that works perfect so that looks better so we have the quarter let's add one more for month and we're going to say month.
Use the month function but as you notice this actually gives the month number so i'm going to call it month num we are going to need this column.
And create a new column which will have actually a text month january february march i'm just going to call it month and the simplest way is to say format.
The date and i like three letter months so j a n instead of january and you know we have that now this is our date table so now we can go back.
And i'm going to format this date column to be simpler i like having uh the short date format instead of this really long one but really that's not the one we're.
Interested in the power of the calendar table is in all the other fields the year year month day so let's uh put those in here so year.
Quarter month perfect and what happened here i should have had my auto date time.
Here oh i think oh i know what happened since i connected it to a calendar table notice that the auto date time went away which makes sense now we have the.
Calendar table but of course if we go back in the video the auto date time was working it had the auto hierarchy the year date month so here we have 2019.
But you see there's something i miss here the months are not really sorted right and of course if you remove the quarter you can see quite easily oops.
That they're actually alphabetical but that's an easy fix especially because because we have the month number in there so you just go to the column and say month.
Column tools and sort by column so sorted by the month number so january becomes one february becomes two and if you go back we can see everything is.
Sorted correctly and if you like we can bring in the quarter and now you can see inside that quarter it displays correctly so that's the second way to do it in dax let me show.
You the third one and then we'll talk about what's the best approach so the third method to create your calendar table is in the incredible.
Kitchen of power bi which i talk about it extensively in my 60 minute power bi tutorial if you haven't seen that make sure to check that out especially if you're getting started so power query is.
The kitchen of power bi and you can open that by clicking on the transform data button uh if it were up to me i'll put the kitchen symbol icon or name it kitchen and power bi but transform data.