Power BI basics: How to create a simple, dynamic and flexible dates table

22nd Aug 2019

istock_matejmo_ys

Almost two years ago, I wrote this article about setting up your dates table in Power BI. Since then, and many Power BI reports later, I have more or less standardised on a structure for my dates table that works really well for me.

I say more or less, because every so often I come across a minor tweak that I need to do for a particular report.

The structure that I will explain here is what I teach on my one-day course “An introduction to Power BI for accountants”. I find that it meets the basic requirements of a dates table for financial data and you can extend it easily if you have further needs.

Basic requirements for a dates table

Your dates table should:

Comprise a contiguous set of dates covering the full period that you want to analyse. Generally, these should be dates, though if you only ever report by period then you can use periods as the finest level of granularity.

Encompass all dates that you may ever reference with the DATEADD function (try Googling something like “DATEADD not working properly”). In practice, I find that if I extend the dates table as far into the future as it goes back into the past, then I am covered.

Handle your year-end in the event that it is not December 31st. If you are an accountant producing reports for many different clients, then you also want a very simple way to adjust the year-end definition.

How to create your dates table using DAX

CALENDAR will enable you to create a ‘dates’ table that spans specified start and end dates.

CALENDARAUTO scans the data in your model and automatically creates a table that spans the lowest and highest dates that Power BI finds in your data.

It is tempting to use CALENDARAUTO, but I always use CALENDAR. One reason for this is to include future dates for forecast transactions. Another reason is to avoid any errors if I use the DATEADD function. Additionally, many datasets contain rogue dates such as 1900 that would create an unnecessarily large table.

Create a dates table using the DAX CALENDAR function

In the Modeling tab in Power BI, select “New Table”, then enter the following DAX command:

This will create a single column table called ‘Dates’ with a column called ‘Dates’[Date] that goes back in time by 1,000 days and forward by 1,000 days. You can obviously customise the date range to your particular needs, but I would recommend always going into the future by as many dates in the past to avoid unexpected issues with the DATEADD function.

Using TODAY() keeps the table dynamic, so each day the entire table moves forward by one day.

Setting the Year End

Somewhere in your model, you will need to define your year-end. If you do this in one place then you can easily change this for other companies. A simple method is just to create a measure [YeaEndMonth] such as:

YearEndMonth := 3

This effectively defines a constant in my model called [YeaEndMonth], in this case with a value of 3, corresponding to March. If my dataset already contains year-end information then I can simply adjust this measure.

Dates[FinancialYear] column

Having defined the year-end month, you can create a new column Dates[FinancialYear] as follows:

This sets the Dates[FinancialYear] value for each date, to the calendar year of the final month of its financial year.

Dates[Month] column

The purpose of the Dates[Month] column is to provide the abbreviated form of the month name such as “Jan”, “Feb”, “Mar” etc.. This is to save space in your report without losing any clarity. You can use the DAX FORMAT function to do this.

Dates[Period] column

The Dates[Period] column contains the period number 1 to 12 for each month. For example, if the year-end is 31st March, then April will be period 1.

The main purpose of this column is to enable you to sort our Dates[Month] column correctly. This video shows you how to do this.

Dates[RelativeFY] column

The Dates[RelativeFY] column contains the relative financial year of any date compared with that of today. So for example, if today is 21st August 2019 and our year-end is 31st March, then the current financial year is 2020 and our Dates[RelativeFY] column should look like this:

FinancialYear

RelativeFY

2018

-2

2019

-1

2020

0

2021

1

This Dates[RelativeFY] value enables us to set dynamic filters in our report. For example, a filter for Dates[RelativeFY] = 0 or -1 will always filter for the current or previous financial year.

To create this column, we first need to define the current financial year. We can do this with a measure [CurrentFY] as follows:

Once we have the [CurrentFY] defined, we can create our Dates[RelativeFY] column:

Dates[RelativeMonth] column

The Dates[RelativeMonth] column contains the relative month of any date compared with that of today. So for example, if today is 21st August 2019, all dates in August 2019 will have the value Dates[RelativeMonth] = 0; all dates in July 2019 will have the value Dates[RelativeMonth] = -1 and so on.

This column serves two purposes. Firstly, it is extremely useful for dynamic calculations over particular month ranges – for example rolling 12 months up to the end of last month will have a Dates[RelativeMonth] value in the range -12 to -1. Secondly, it provides a column against which we can sort our dates table by month. The following DAX calculated column will create Dates[RelativeMonth]:

Dates[MonthYear] column

The Dates[MonthYear] column contains the abbreviated month-year value in the form “MMM”-“YY” so, for example, 21st August 2019 has the Dates[MonthYear] value of “Aug-19”. We can use the FORMAT function to create this calculated column as follows:

If we are always presenting our report based on our financial years, then we do not need this column. However, should we want to display say a matrix or column chart showing rolling 12 months then our months will typically span more than one financial year and we can use this column (sorted by Dates[RelativeMonth] ).

Summary

In this article, I have presented a simple yet very flexible dates table that you can adjust easily to your financial year as well as support rolling calculations such as rolling 12 months. You can create your own custom date hierarchy based on your financial year and extend it with columns such as Dates[RelativeWeek], Dates[RelativeQurter], Dates[Quarter], Dates[AgedPeriod] etc. as you need.

I am a founder director of Accounting Insights Ltd , a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs, sales reports & forecasts, liquidity & cash flow reports,...