Director Accounting Insights Ltd.
Share this content

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

22nd Aug 2019
Year chart
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. 

  • Sort your months correctly based on your financial year.

  • Enable your reports to roll over from one year to the next without collapsing and waiting for you to edit hard-coded filters and values.

  • Provide you with a basis for rolling analysis – such as rolling 12 months or aged debt periods.

Dates table starting structure 

Column 

Type 

Description 

Date 

Date 

A column of contiguous dates 

FinancialYear 

Whole number 

A whole number that describes the financial year – e.g. 2019, 2020 etc. 

Month 

Text 

The short month name e.g. “Jan”, “Feb”, “Mar”, “Apr”. 

Period 

Whole number 

A whole number 1 to 12 that identifies which accounting period the date falls into. 

RelativeFY 

Whole number 

0 means that the date is in the current financial year, -1 that it is in the previous financial year etc. 

RelativeMonth 

Whole number 

0 means that the date is in the current month, -1 that it is in the previous month etc. 

MonthYear 

Text 

An abbreviated form of Month and calendar year – e.g. “Apr – 20” 

I then extend it if needed, with Week, RelativeWeek, Quarter, RelativeQuarter, AgedPeriod, AgedWeeks for example. 

How to create your dates table using DAX  

The two most common DAX functions to create a ‘dates’ table are CALENDAR and CALENDARAUTO

  • 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 

2021 

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. 

Useful links 

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.