Business Intelligence

Auto-generating Date Tables in Power BI Desktop

By June 20, 2017 February 7th, 2019 No Comments

It’s common practice for data-centric companies to have a standard date table to use for reporting purposes with all kinds of useful fields to measure performance over time.

Hide in report view image

But what if you – or other users of the report – don’t have access to that date table? Power BI has a built in function that allows you to generate a date table on the fly. Combine that function with a couple of DAX formulas and parameters in the Query Editor window, and you get a reusable template you can use for almost all of your time intelligence needs.

Creating Base Date Table in Power BI

In an empty Power BI Desktop file, under the “Modeling” tab, click on the “New Table” button.

New table image

This will cause the formula bar to appear, where you can enter the following formula and hit enter.

Dates = CALENDAR(“1/1/2000”, “12/31/2050”)

If you click on the data view and look at the newly created Dates table, you’ll see there is now a date table with sequential dates from January 1st, 2000 to December 31st, 2050.

Adding Simple Columns to the Date Table

There are a handful of fields that are essential to any date table, including month, month name and year. These can easily be added using a few simple DAX formulas and built-in properties. First, let’s add the non-fiscal columns. Simply click the “New Column” button on the “Modeling” tab and enter the following formulas.

Day = Dates[Date].[Day]

Month = Dates[Date].[Month]

MonthNo = Dates[Date].[MonthNo]

Year = Dates[Date].[Year]

New basics columns image

Note that these formulas don’t require fancy functions – they’re just built-in properties that all date fields contain. We added in MonthNo because we want to use it as the “Sort By Column” for the Month field. Just select the Month column, go to the Modeling ribbon, click the “Sort By Column” and select MonthNo from the drop down. Adding an ID or Number field to determine sort order is the best practice to implement a default sort order for columns that may not sort sequentially or chronologically with the default alphabetical sorting.

Sort by month image

Adding Fiscal Columns to the Date Table

For many organizations, fiscal years don’t run concurrently with the calendar year. For those fiscal periods, you need special columns that group months into the right fiscal years in addition to alternate sorting fields.

For this example, we will assume that fiscal 2018 starts on July 1st, 2017. As before, use the “New Column” button and enter the following formulas.

FiscalMonth = Dates[Month]

FiscalMonthNo = IF(Dates[MonthNo] = 7, IF(Dates[Date] >= 1, 1, 13), IF(Dates[MonthNo] > 7, 1 + Dates[MonthNo] – 7, 13 + Dates[MonthNo] – 7))

FiscalYearNo = IF(OR(Dates[MonthNo] > 7, AND(Dates[MonthNo] = 7, Dates[Day] >= 1)), Dates[Year] + 1, Dates[Year] + (1 – 1))

FiscalYear = “FY ” & Dates[FiscalYearNo]

The number 7 in the formulas above represent the chosen start date of the fiscal year – if you are using a date other than July 1st, update these accordingly with the correct calendar month number. The bold 1sindicate that July 2017 starts fiscal year 2018 – if July 2017 starts fiscal year 2017, change the 1s to 0s.

Fiscal columns image

After getting the fiscal columns added, you need to change the sort order of the FiscalMonth and FiscalYear columns like before – sorting them on FiscalMonthNo and FiscalYearNo, respectively.

At this point, you have a robust date table to use in Power BI reports. The extra step after this point is to create a Power BI template file with parameters that can be reused and shared so that you and your colleagues don’t have to repeat the above steps every time.

Creating a Power BI Template File With Parameters

The first step in creating the template file is to add parameters via the Query Editor window. To get to this window, click the “Edit Queries” button on the “Home” tab.

Edit Queries image

In the Query Editor window, click on the “Manage Parameters” button in the “Home” tab. This pops up a form that lets you create and configure parameters.

For this template file, we are going to need four parameters; StartDate, EndDate, FiscalYearStartDate, and FiscalYearOffset.

Set the first three to a Date data type with their Current Value set to the values we used before:

StartDate = 1/1/2000 EndDate = 12/31/2050 FiscalYearStartDate = 7/1/2017

FiscalYearOffset should be configure as shown in the picture below.

Query editor parameters image

After clicking “OK”, you will see the four parameters in the left-hand side, with their current values in parenthesis. By default, parameters don’t get loaded into the report, which is where we need them. To rectify this, right click on each parameter and select “Enable Load”. As you do this for each parameter, the text will change from italic to normal. Once you’re done with that, click the “Close & Apply” button in the “Home” tab.

Query Editor image

Now that we have these parameters loaded into the report, we will need to update the formulas for the fiscal month and fiscal year columns to utilize the parameters. In the formulas listed above, there were hard-coded numbers (7 and 1) – these will be replaced with the data from the parameters. One oddity here is that the parameters get loaded as single-cell tables, so we will have to use a summarizing function to get a single value.

There is one more thing we need to do before we can update the formulas – we need to add a couple of extra columns to the FiscalYearStartDate table. With that table selected in the Fields pane, click the “New Column” button and enter the following formulas.

FiscalYearStartMonth = FiscalYearStartDate[FiscalYearStartDate].[MonthNo]

FiscalYearStartDay = FiscalYearStartDate[FiscalYearStartDate].[Day]

Now select the Dates table and updated the formulas from FiscalMonthNo and FiscalYearNo to the following.

FiscalMonthNo = IF(Dates[MonthNo] = MAX(FiscalYearStartDate[FiscalYearStartMonth]), IF(Dates[Day] >= MAX(FiscalYearStartDate[FiscalYearStartDay]), 1, 13), IF(Dates[MonthNo] > MAX(FiscalYearStartDate[FiscalYearStartMonth]), 1 + Dates[MonthNo] – MAX(FiscalYearStartDate[FiscalYearStartMonth]), 13 + Dates[MonthNo] – MAX(FiscalYearStartDate[FiscalYearStartMonth])))

FiscalYearNo = IF(OR(Dates[MonthNo] > MAX(FiscalYearStartDate[FiscalYearStartMonth]), AND(Dates[MonthNo] = MAX(FiscalYearStartDate[FiscalYearStartMonth]), Dates[Day] >= MAX(FiscalYearStartDate[FiscalYearStartDay]))), Dates[Year] + MAX(FiscalYearOffset[FiscalYearOffset]), Dates[Year] + (MAX(FiscalYearOffset[FiscalYearOffset]) – 1))

To utilize the start and end date parameters, select the Date column in the Dates table and update the formula to the following.

Dates = CALENDAR(MAX(StartDate[StartDate]), MAX(EndDate[EndDate]))

You can also hide some of the unnecessary columns that will not be needed in visualizations (like MonthNo, FiscalMonthNo and FiscalYearNo) to make the tables easier to use. This is a best practice for all models, not just date tables. In the Data view, simply right click on the column header and select “Hide in Report View”. As you do this, you will notice that the text in the column turns a lighter grey color.

Hide in report view image

Now that the file is ready to be a template, the last thing to do is save it as such. There are two ways to save this as a template file:

  1. Click on “File” and then go to “Export” and click on “Power BI template”. You will get a small dialog box asking for a description of the template; just enter in a description that is meaningful to you about what this template is, or you can leave it blank.
  2. Click on “File” and then click on “Save As”. In the “Save As” dialog box, change the “Save as type” to “Power BI template file (*.pbit)”
Export as template image

Using the Template file

When you open the template file you just created, you will see a dialog window asking for the values of the parameters. Entering in the desired values and clicking “Load” gives you a great starting place for your Power BI report.

Date table template image

And that’s it. Now start sharing the template file and leveraging all the Time Intelligence functions Power BI has to offer.

Joe Gravelyn

Author Joe Gravelyn

More posts by Joe Gravelyn