Power BI: How and why to create a date table
Once you have got your Sage 50 data into Power BI Desktop, setting up a good date table is probably the next and most useful thing you need to to do. It will save you time and you will be able to do four useful things with it:
- Define analysis periods that you can reuse throughout your Power BI model
- Ensure that you can use the Date Hierarchy function in Power BI
- Analyse different transaction types together (e.g. Sales Invoices and Sales Receipts)
- Mash-up unconnected data sets like Google Analytics with your Sage 50 data
Even just to display a simple chart of sales this year versus last year it makes sense to use a date table. In fact, this article will step you through what you need to do to create this chart from your Sage 50 data.
What is a date table?
A date table can be described as having three core elements:
- A set of unique and contiguous dates that includes the entire date range over which you wish to analyse your Sage 50 data
- Relationships with your Sage 50 transaction dates
- A set of calculated columns that define analysis periods that you want to use for summary calculations throughout your model
Step 1 - Create a single column date table
There are different ways to create your single column date table in Power BI Desktop.
- You can import a date table that you have defined outside Power BI, for example in Excel. I don’t recommend this though since the other techniques below are simple and they keep your Dates table definition within your Power BI model.
- You can create a dynamic date table which automatically generates a continuous range of dates between the lowest and the highest date in your dataset. Initially this seems very attractive and efficient, but it has a couple of problems. Firstly if there is a problem with your data and the lowest date is set to 1st Jan 1900 and you end up with an incredibly long table. Secondly (and this is the main reason that I don’t use this method), if you want to do forecasts or projections you are by definition looking beyond the highest date that you have in your Sage 50 dataset.
A third option is to use a simple DAX command to create a date table with a set of contiguous dates relative to today. My Dates table simply has a range of dates +/- 800 days from today. For my purposes +/- 800 days is more than I need. I am not interested in my Sage 50 data that is more than a couple of years old. Nor I am I likely to want to project beyond about a year ahead.
To create this relative date table, in Power BI Desktop go to the Modeling tab and select “New Table”.
In the formula space then simply use the DAX CALENDAR command to create your date table that spans a particular range.
Dates = CALENDAR(TODAY()-800,TODAY()+800)
This creates a single column table called Dates with a column called Date with 1601 rows spanning today minus 800 days to today plus 800 days. You can, of course, adjust this range to suit your own purposes.
Change the Data type
You will see that the Date format is Date/Time. Since I don’t have and nor am I interested in the actual time of my Sage 50 transactions, I change the data type to Date.
Note about regional settings and date formats
I use the *14/03/2001 (dd/MM/yyyy) format because it is compact for display in my model. Also, the asterisk is important. In Power BI, the dates formats marked with an asterisk automatically adjust based on your regional setting. So 14/03/2001 for a UK user will become 03/14/2001 for a US user.
Step 2 - Define relationships with your date Table
You will use your date table to tie together date-driven Sage 50 transactions so that you can see them together on a common dates axis and so that you can reuse any analysis periods that you define for all of your transaction types.
Power BI Desktop offers you two simple ways to create and edit relationships. You can use either.
The first presents a schematic view of your data relationships.
Clicking on the Relationships icon on the left of your Power BI canvas will open this schematic view.
You can see that the relationships I have created are between the date table and my following Sage 50 transaction tables:
- Sales Invoices
- Sales Orders
- Nominal Transaction Details
- Purchase Orders
- Goods Despatched
I did this simply by dragging and dropping the date field in the date table to the transaction date field in each of my transactional tables.
The alternative method in the Modeling tab is to click on the Manage Relationships button.
The next thing you will want to do is to extend your single column date table with additional columns to define common analysis periods.
Each column defines an analysis period (e.g. Last 28 Days) and each row in the column contains a True/False value as to whether or not that Date falls within that analysis period.
What analysis periods you set up and why is a matter of your own choice. You can see in the screenshot the periods that I have set up.
I will discuss these in more detail in a future article, and show you how you can do the same or adapt to meet your own needs.
In the example here, I have an analysis period called Last 28 Days. The Last 28 Days will always be relative to today, so every day the dates flagged as True will increment by one day.
The formula for creating this is a simple DAX command, and can be adapted easily for other time periods.
Last 28 Days = IF([Date]<(TODAY()-28),FALSE(),IF([Date]<TODAY(),TRUE(),FALSE()))
This screenshot was taken on 15th October 2017, so the Last 28 Days are the 28 days from 17th September 2017 to 14th October 2017.
Now that I have defined this analysis period, I can use it to calculate summaries of Sales Orders, Sales Invoices, Purchase Orders, Goods Despatched or Nominal Transactions over the last 28 Days.
This Calendar Year = IF(YEAR([Date])=YEAR(TODAY()),TRUE(),FALSE())
In my model, I have created some additional Measures to determine the first and last day of a company fiscal year. So the formula for This Year is:
This Year = IF([Date] < 'Fiscal Year Dates'[First day of this Fiscal Year],FALSE(),
IF([Date] > 'Fiscal Year Dates'[Last day of this Fiscal Year], FALSE(),
Step 4 - Create summary calculations by analysis period
If you have set up your date table and analysis periods in this way, then with one simple formula you can create lots of really useful Measures, which are a form of summary calculation in Power BI.
The formula is:
- Table is the table that contains the data you want to summarise
- Expression is a valid DAX expression. If you just want to summarise one field (e.g. Sales), just enter the field name here.
- Filter is your analysis period defined in your date table.
So, for example, I can create two Measures Sales This Year and Sales Last Year in the following way:
Sales This Year = CALCULATE(SUMX('Sales Invoice Details',[Sales]),'Dates'[This Year])
Sales Last Year = CALCULATE(SUMX('Sales Invoice Details',[Sales]),'Dates'[Last Year])
Step 5 - Point, click, drag, drop and have fun
Now that I have my date table built, analysis periods defined and some summary Measures for Sales this Year and Sales last Year can now create my graph comparing sales this year with sales last year in three simple steps.
- Select visualisation Clustered Column Chart
- Drag the [Date] field from my new date table to become the axis.
- Drag the Measures [Sales this Year] and [Sales last Year] to the “Value” area of the visualisation definition.
And here I have it.
Summary and to find out more
Setting up a date table in Power BI is really essential if you want to do serious date-driven analysis of your Sage 50 data, and it is in fact very simple to do.
What I have shown you here are the basic steps. In a future article I will go into more detail about what kinds of analysis periods you may wish to set up and how to do this.
You might also be interested in
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,...