Power BI: How and why to create a date table

Financial chart
istock_da-kuk
Share this content
Tags

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.

Power BI

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.

Power BI

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.  

This is the formula that I used:

Dates = CALENDAR(TODAY()-800,TODAY()+800)

Power BI

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.

Power BI

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

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.

 
Power BI

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.

Power BI
This opens up more of a tabular view where I can see, create and edit relationships.
 
Power BI 9
Power BI 10
Power BI

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()))

Power BI

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(),

TRUE()))

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:

CALCULATE(SUMX(Table, Expression),Filter)

  • 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.

  1. Select visualisation Clustered Column Chart
  2. Drag the [Date] field from my new date table to become the axis.
  3. Drag the Measures [Sales this Year] and [Sales last Year] to the “Value” area of the visualisation definition.
Power BI

And here I have it.

Power BI
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.

Useful links

About Hugh Johnson

Hugh Johnson

I am the creator of Accounting Insights for Sage 50 which is the only Microsoft Power BI Content Pack available for Sage 50.  Please feel free to try it out in connected to your own Sage 50 system on the web or with Power BI Desktop.

I am a senior vice president of Suntico, an online financial data platform that works with popular small business accounting software.  I am responsible for the company’s customer division. This covers products, marketing, sales, customer on-boarding, customer service and renewals. I am also in charge of our product strategy and road map.

I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year). 

My personal passions are high performance Fireball dinghy racing and food.

Replies

Please login or register to join the discussion.

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