Save content
Have you found this content useful? Use the button above to save it to your profile.
Financial chart
istock_da-kuk

Power BI: How and why to create a date table

by
18th Oct 2017
Save content
Have you found this content useful? Use the button above to save it to your profile.

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

Tags:

Replies (6)

Please login or register to join the discussion.

avatar
By Manchester_man
18th Apr 2018 05:11

Nice article. Would this work in the following way:
We want to use the Xero content pack, but there is a bug / fault with the way dates are laid out.

Could I use the instructions above to add date tables and then report on the Xero content pack data using the dates in the date table?

Thanks (1)
Replying to Manchester_man:
Hugh Johnson
By Hugh Johnson
18th Apr 2018 18:05

For this to work with your Xero content pack you would need to be able to bring the data into Power BI Desktop. I have not tried this with the Xero data feed for the content pack, but I suspect that you can.
Assuming this is the case then you could create the Dates table in Desktop as described here and link it to your Xero transactions.
The downside is that you would need to dispense with the content pack ad create your own model and report in Desktop. From here you could then publish to the web and use it as you would have used the original content pack.
The upside is that you now have a model that you can customise to your heart's content :)
Hugh

Thanks (0)
Replying to Manchester_man:
Hugh Johnson
By Hugh Johnson
19th Dec 2018 15:15

Hi Manchester_man,

What I have seen on the various boards is that you cannot pull the data into PBI Desktop. Even if you could, then you could hit difficulties around the Xero API throttling and data transfer limits.

My company is quite advanced in building a Xero connector with a data warehouse in Azure that gets around these issues and also delivers the data in a form more suitable for analysis in Power BI.

We have got as far as we can without working with a client and real data. If you are interested in collaboration please reach out to me on LinkedIn.

Thanks, Hugh

Thanks (0)
Replying to Manchester_man:
Hugh Johnson
By Hugh Johnson
19th Dec 2018 15:15

Hi Manchester_man,

What I have seen on the various boards is that you cannot pull the data into PBI Desktop. Even if you could, then you could hit difficulties around the Xero API throttling and data transfer limits.

My company is quite advanced in building a Xero connector with a data warehouse in Azure that gets around these issues and also delivers the data in a form more suitable for analysis in Power BI.

We have got as far as we can without working with a client and real data. If you are interested in collaboration please reach out to me on LinkedIn.

Thanks, Hugh

Thanks (0)
Hugh Johnson
By Hugh Johnson
18th Apr 2018 18:19

I have lately had some second thoughts about pre-defining so many analysis periods in the Dates table. I guess it depends on what you are trying to achieve. I wrote this in the context of publishing a content pack. In this context I think it is a good idea because it gives the end-user a lot of scope to customise a report based on different date filters. It is also a good idea because a period (e.g. the last 28 days) is only defined in one place, so it makes for a more understandable and reliable model. Recently though, I have been working with some very large Sage 50 datasets (north of 500,000 records) and this approach can (I believe) contribute to performance issues in Power BI Desktop. The reason is that you can end up defining a lot of extra calculated columns that are there "just in case". All of these columns will take up extra memory in your loaded model whether you are using them or not. In such a case, I would suggest that moving many of the date filtering calculations to within each summary meaure would help the model to perform better in Power BI Desktop.

Thanks (0)
Hugh Johnson
By Hugh Johnson
04th Dec 2018 09:15

@Kumar,

Another thing you could use for your Aged Periods is the Grouping and Binning functionality in Power BI Desktop. https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

This would only work well though if your bins are all the same size (e.g. 30 days).

Thanks (0)