Power Pivot Calendar Table with DAX function

Alright, you can see how naggy I can be if you have read my last article. In the last article I talked about how important it is to create a calendar table in Power Pivot. It is almost compulsory for this table to exist while you are data modelling with Power Pivot. Well, you can see how I am still a bit reserve (I love saying almost) when I make certain statement. Reason is because somehow there will be some trolls who will jump out and argue with the statement I made. Anyhow it is very subjective still because you know with how the way Microsoft launch new updates and make changes every now and then, I better be a bit reserve.

Nearly all analysis and reports involve the comparison of what’s going on from certain time to time, what happened last year, let’s compare the data from year to date and I can go on and on and on. No matter what sort of data that you’re collecting, almost all the facts data are collecting data base of date and time. With this calendar table, you will be able compare base on fiscal year, quarter, MonthNum, Fiscal Month and others.

Later on, I will also walk you through how you can create hierarchy using these fields. With this, you will be able to drill down/ up your reports and charts.

This is how the calendar table looks like. Financial Year in this example begins from July.

Anyways, let’s get this calendar table started.

Create the date into the Calendar Table

First and foremost, the most important column is the first column, date column. This column should contain all date in the calendar. What dates should stay in this column also depends on the data that you’re analysing. If the data that you’re analysing are from 1 July 2010 to 30 Jun 2014, then the date in this column should at least starts from 1 Jan 2010 to 31 Dec 2014. With this sets of data, we want to make sure it is dynamic, which means you gets to choose what period of date to be imported into this table.

Adding date into the Data Model

There are many ways you can add date into the Data Model.

Ways you can import data into Data Model:

  • Import from relational database
  • Excel Table
  • Microsoft Azure marketplace

But what I’m going to talk about is to use Excel Table. We’re going to create an Excel Table, save it in a hidden place. Simply because it is easy, and you do not need to have other software or license besides having Excel.

We are going to create dates from year 2000 to 2040 in this Excel workbook. This is how you’re going to create it.

Start out with a blank Excel workbook and label the header as Date, then enter this date into the first cell 1/1/2000.

To generate dates list, right click the fill handle drag one cell down then up. I know it sounds weird, but…just do it kay. Drag down then up, you should be able to see a mini dialog box. In the selection, go all the way and select the last option “Series”.

Once you see the series dialog box, select Column, Date and Day. Finally fill in 31/12/2040 in the stop value placeholder.

Once the dates are generated in the Date column, convert them into an Excel Table. Make sure you name the table properly, so I’ve named my table as U_Date like unique date. Don’t forget to name the worksheet as well.

Now save your workbook as Excel Workbook in some secret folder and let it rest in peace. Likely, you will not need to open this file anymore.

After you have completed saving them, close the file. Follow by opening a new Excel blank workbook. Immediately dive into the Data Model. Now we’re going to set a connection to the Excel workbook that you just saved using the From Other Sources then select Excel File.

After that, you will have to browse to the Date workbook you just saved from earlier, select the worksheet that contains the date.

So, as you can see, we have generated tons of date in the Excel Workbook that we’re connecting to. We definitely do not need all of them, which means you can filter the list, import only what you need to the Data Model window. From time to time, you can change the filter settings, for sure.

Continue our part 2 here.

Leave a Reply

Your email address will not be published. Required fields are marked *