How to Create a Date Table in Power BI Desktop

Create a custom date table in Power BI Desktop

In this video, you will learn how to create a custom date table to your data model to use time intelligence functions in Power BI Desktop. You will use DAX to create the table and the Model view to connect it to your data model.

Hi, this is Matt with Playfair+. In today’s video, I’m going to walk you through creating a custom date table. Now when you get your data, you don’t always have a full date table in it, and it doesn’t allow for the granular, time series analysis that you would want to do. So we’re going to get around that when creating our own new date table and adding that to your data structure.

So, we’ll walk through all the steps today, show you some examples of how to use it and get you off and running. Let’s dive in. Let’s walk through building out our date dimension table. And we’re going to walk through how we do it and what the formatting is, and the reasons that we would build this out.

It just allows for that more flexibility, so. Our custom date dimension table will also have a fiscal year and calendar year breakdown. If you want to do an analysis and your fiscal year and your calendar year are not aligned, they don’t both start in January, this is a really easy way of preparing your data to handle that.

So, this breaks your date table into those custom date parts. So we’re going to get all the way down to the day granularity. And then we’re going to use those time intelligence functions in the future to analyze some data. So, let’s go ahead and we’ll click on Table view.

To take a look at what we’re doing and we can do it in Table view, we can also do this in Power Query. So, let’s start with Table view. And what we’re going to do is we’re going to create a new table. So this is going to open up our M code, our DAX, and it’s going to allow us to create the new table.

And I’m realizing now that I don’t have a second monitor to drag in these calculations right off the bat. So I’m going to use the follow along that has the date table created, and I’ll walk through each of the steps that we cover in our slides here. So the first thing we’re going to do is we’re going to, let’s copy and paste this in.

I think it would be too much for everybody to type all at once. I know everybody types at different speeds and I want to make sure we get through all of the DAX information that we have today. But this, we’re going to call it Dim_Date, it’s dimension table. So we’re going to call it Dim_Date. That’s the name of the table is Dim_Date.

And then we’re going to use variable for the first time here, VAR. So it creates that variable that we can reference in our DAX calculations instead of writing out a function over and over and over again if we need to make a change. We only have to change the variable, and then everything that’s referenced from that variable will change automatically.

So, it’s really nice and handy. We like to use variables so that we can create a new feature without having to iteratively write over and over and over again at a column for year, add a column for month. We can just do it once and we will ask the DAX to do the hard work for us.

So, we’re creating our new table variable, _ calendar. We do it underscore so that it doesn’t actually call the CALENDAR() function. And then we will equal the calendar function and then the date range. So this is MIN(), we’re going to say date, from the date table. And then MAX() is the date from the existing date table in the AdventureWorks data set.

You might not have a separate table. For the other data sets that you work with, but you probably have a date range. You would set the min and max date, to those as well. For future reference. Then we’re going to use RETURN and because we’re creating a new table, we want to add these columns in, so we want to add columns to our new calendar, and it’s going to be, we want a year part, a month part, a week part, and a day part.

So we want to have all of those created. And so that’s going to create year, month, week is WEEKNUM(), and then DAY() for day. And then once you’re done with that, you should see those columns appear. And if we switch back over to our Power BI view, we should have date, year, month, week, and day, all created at this point.

So, I can’t select it in groups in Table view, but these first five columns should be created at this point. So. Alright, moving forward from that, and you can see that we’ve got month, week, day, we’re going to create fiscal year. So now we’re going to create the separation between the calendar year and fiscal year.

And to do this, we’re going to set our fiscal year, start month to July. So it’s set at seven in our prebuilt calculation. So, using that variable. If we ever needed to change the reference in here, we wouldn’t have to go and rewrite this every single time. We would simply have to come in and say, you know what?

I want my fiscal month to start in May. So we changed the variable to five. Um, and then the condition, excuse me. Is an IF() statement and the IF() statement is, if the date month here is greater than or equal to that variable, FYstartmonth, seven, then we’re going to say, we’re going to take the date and we’re going to add one.

So we’re going to take the year and move it forward because that fiscal year is going to be bumped forward. And then anything below, so anything June, prior is going to be the same fiscal year. So. Then we’re going to return and with the value that we want to be returning is in quotes, “FY”, and that FYcondition.

So is it going to be 2020 or 2019 based on the existing date? This is the return you’d get. So it’s this column on fiscal year. We have our date. You can see January 1^st, 2020 is fiscal year 2020, and as I scroll down, you’ll start seeing the change. So like if I look at November 2020, well that’s actually in fiscal year 2021 because it’s after the start of the new fiscal year, which was July.

So we’ve bumped things forward into the new fiscal year. So that’s how we create this fiscal year calendar. Now the next slide is going to show us the fiscal month number. And we need to do this because we don’t want to just have, Hey, what fiscal year are we in? We need to actually renumber the months. Now you’re thinking January one, all the way to December, 12.

Um, but we just have to shift those forward seven. So, six. So if you, if you see the calculation, you see, alright, our fiscal month number, the variable is seven. Now we’re going to return. What the month number is, so the start month minus six or plus six, and that’s going to generate our actual, what fiscal month number we’re in.

And you can see as I scroll through, we’ll get all the way down to our switch to July and you can see. This is a date July 1^st, 2018. We’re now in fiscal year 2019 because the fiscal year has started in July and our fiscal month number is one. So it’s like pretending, not pretending it’s, it is making July the first start month of the fiscal year.

So that’s how we do our fiscal month number calculation, and I know I’m flying through this, we’ve only got about 40 minutes left today and we’ve got so much DAX I want to share with you guys. So, the recording will be available for another 24 hours. So if for some reason I’m flying through this section and you’re saying, man, we’ve got to slow down, we’ve got to do more of the fiscal month number, fiscal month date, I’ve got to know more.

You can go back to the recording. And then we’re going to create a fiscal quarter as well. So that’s going to be another piece that we pull together because we want to make sure we’re showing what the fiscal year is and what quarter we’re in in that fiscal year. This is going to be an introduction to the SWITCH() function.

So this is a bit of DAX, where we’re calling this column fiscal quarter. And what we’re doing is this is basically, if you’re familiar with Tableau, this is a CASE() statement CASE-WHEN is very similar to SWITCH()-TRUE(). So we’re saying SWITCH() and when the next row is TRUE(). We’re going to give a return, so then we’re looking at the fiscal month number from our dimension date table when it is greater than or equal to one and less than or equal to three, that’s Q1. And then we’re going to iterate through these rows, Q2, Q3, and Q4. And then it will return the value of, we want it to say fiscal year and, and then we have in quotations space, whatever quarter it is.

So that’s how you get the, the return value in our fiscal quarter column of, January 1^st, 2020 is fiscal year 2020 Q3. So. Alright, because we’re looking at the fiscal month number. It’s a seven. It falls between that seven and nine. There it is, the Q3. And then the last bit of information we need for our new date dimension table is a date key.

We want to be able to link this new table that we’ve created back to all the other tables and especially to our Sales table. And so we want to make sure that we have a date key that’s going to link to the date key that we already have in that existing table. To create that, it’s just a single line of code.

It’s going to be format. We’re going to format the date as “yyyymmdd”. And that’s the format that our date key will take. So you can see 20200101 for the date of January 1^st, 2020. And that is the same that we have in our, let’s say we’ll go to our Sales table and you can see our order date key, our ship date key, they’re all done in that same format of yyyymmdd.

Now, the last step that we need to take is we need to go and we need to connect our new dimension, date table to the Sales table. And we are going to do that by taking our DateKey and dragging it onto something like OrderDateKey on the Sales table. I’ve already done it. You can see when I click on the connection, you see our Sales table, column, OrderDateKey, dimension, date table, DateKey, make this relationship active, and there we go.

So this is listed as many-to-one simply because. I started with the Sales table. If I flipped the orders, if the dimension date table is listed up top, it would say one-to-many. So we want to make sure that that direction is moving into the many side of sales. This has been Matt with Playfair+.

Thanks for watching our date table tutorial.