Most businesses need to create calculations based on time to track changes over days and months and years. So it's important that our model has a dimension table dedicated to parsing out and grouping time increments that are relevant for our business. There are different ways to get a date table. Often data warehouse developers will have already created a date table for your model. In these cases, you should use the warehouse model because it likely has appropriate time intervals and company dates, alternative year, start and end dates. But not everyone has this at their disposal. So let's learn how to build one ourselves.

Among the library of helpful data analysis expressions in Power BI or a group of time related functions to help you start off a date table. We have CALENDARAUTO and CALENDAR. These DAX both let you kick off a date table from scratch. First, let's create a table by going to the data view and opening the table tab of the ribbon and selecting new table. Let's start with CALENDARAUTO. I'll type in equals CALENDARAUTO and open the parenths. And I can see from this tool set that the only argument required is to define the fiscal year end date. So I'll type in 12 for December. Press enter and just like that, this DAX function identified the oldest and the newest dates in our model and populated a calendar between them.

Now, usually this works well, but every once in a while you'll end up with a calendar starting in 1901. So it's good to know how to define the outside dates when you need to. This time we'll use CALENDAR. I'll type in equals calendar and I'll open my parenths and this function requires the start date and the end date. But it's looking for the date serial numbers and I have no idea what that is. So I'll use the DATE DAX to figure out the serial number for me, which works just like the DATE function in Excel. I'll type in year and month and day of the start date. And let's do the end date in the same way. And press enter. Great, now I have a custom calendar.

Once the table has been started, we can now add the categorical labels that our users will want like year and month. I'll just set a couple that showed look familiar, like year. Equals year, open parenths and then I'll just indicate where this year DAX should get its date from. And we have years now, let's do months, which works the same way. And hit enter, and I have my months. And while this was fairly using DAX, it's also possible to do this with Power Query which comes with its benefits.

One of the best reasons for choosing to do this in Power Query is that it is easier to populate all of the label columns and Power Query writes the code for you in its language, which is simply named M and after you've developed a calendar and Power Query you can actually replicate the calendar anywhere else by simply copying and then pasting the M language.

Let's head over to Power Query by clicking on transform data on the home ribbon tab. And we can launch a new blank query using new source blank query. This looks pretty blank of course, and we will use the formula bar to enter our M formula. We'll start with a couple of M functions here, equals list.dates, which will create a list of dates of course. M is actually case sensitive, so I'm being super vigilant here. And then I'll open up our function parentheses to enter the dates. This also is gonna be looking for a serial number which I don't know off the top of my head.

So we're gonna use a function in M called date which looks like this, hash date, with a lowercase D, open my parenths, and then just like the Excel function, I need to type in the year, the months, the day, we'll close that up and enter comma to move to the next argument. The second argument of the list dates function is the number of entries we want in this list. So the number of days we want in our calendar. I want this list to have 10 years worth of dates. So I'll just type 365 times 10 and I'll follow that with a comma.

The last argument is where we define the length of each entry, and we want this to be a list of days. And I have no idea how to serially enter a day. So I'm gonna use the DURATION function, which is another of those functions that are not capitalized and they're proceeded by a hash. Hash duration, open the parenths and I'll type one day, zero hours, zero minutes, and zero seconds and close that parenths and execute. 

Looks good, we'll make it official by selecting convert to table from the home ribbon tab. And I'll just say, no delimiters and I want the hanging columns to show up as errors but we won't have any of those. Now we just need to rename this column and I'm gonna be very sure to confirm the data type is date which is super important. We can do this on the home tab, in the transform group, data type, date.

Okay, now we can easily add metadata from the add column tab date. Let's add year and months, and month's name, quarter of year, day and name of day. After you get started in Power Query, reading these labels is so much easier. Okay it looks good, let's close and load it to the model. Great, we have our date table. Now we just need to relate it to our model. And before we forget, let's make sure to mark this table as the official date table by right clicking on the table and choosing mark as date table. When we do this, Power BI fortifies this table against common issues and confirms it meets the requirements to function properly as the date table.