Compare Time periods using a Date Table in PowerBI

Use a Time Master Data

In my previous blog post about PowerBI Best Practices, I insisted on the importance to separate your Measures from your Attributes and to store Attributes in Master Data tables.

This allows you to slice your data on several tables and therefore on several visuals at the same time, which is what you very much want to do when exploring data with PowerBI.

When we speak about Attributes in a Master Data table, we usually speak about Products, about Stores, but we often forget an important dimension: TIME!

In fact each Time Period could be the Key to a Master Data table and come with Attributes as well. We recommend you build such a Date MasterData table and connect it to all the tables with your measures. Here are some examples on what you can do with this:

Example 1: YTD comparison

Let’s say we are in October 2017 and we want to compare YTD (Year To Date) Sales meaning the Sales of January 2017 to September 2017 vs. the same period last year (Jan 2016 to Sept 2016). That is a very common problem, so how can we handle it with PowerBI?

One of the top solutions is actually to create a Time Master Data table which will take each date as a Key and, with a YTD Index which will take “YTD N” as value for the dates in the YTD of the current year (Jan 2017 to Sept 2017) and “YTD N-1” as value for the dates in the YTD of the year before (Jan 2016 to Sept 2016).

Then you can compare your Sales YTD 2017 vs. 2016 building the following measures:

Sales_YTD_N = CALCULATE (SUM (Sales), MonthyMaster[YTDIndex] = “YTD N” )

Sales_YTD_N_1 = CALCULATE (SUM (Sales), MonthyMaster[YTDIndex] = “YTD N-1” )

YTDSales_% = [Sales_YTD_N] / [Sales_YTD_N_1] – 1

Example 2: Comparing YTD monthly trends on a Line Chart

Let’s say we would like to build the following Line Chart for our Sales, with the month as X-axis and the Sales per Year as Y-Axis

Capture d_écran 2017-10-30 à 20.16.57

Again the Monthly Master data could come handy, if we build some columns Year and Month as dates attributes in the Monthly MasterData

Other Examples

You can also use the last Closed month Index, the current month index, Quarters, Half-Years, rolling indexes and so on…

One I find really useful is the Relative Month index. It works as follow:

  • 0 for Current month
  • 1 for next month, 2 for Month N+2 etc…
  • -1 for last past month, -2 for 2 months ago

This is quite useful for building rolling indexes. Let’s say you want to calculate your sales for the last 12 months. You can do this with PowerBI using the following DAX formula

Sales_Last12m = CALCULATE (SUM(Sales), MonthlyMaster[RelativeMonth]<=-1, MonthlyMaster[RelativeMonth]>=-12 )

OK, you convinced me to user a Time Master Data, now how do I get one?

There are many mays to build Time indexes, especially using M language in the Query Editor

I think this is by far the best option for many reasons:

  • a date Master Data easily fits in an Excel or CSV file
  • it’s not report dependent. You can use the same table for many reports so why not build it once for all and import it in 2 clicks in every Report you will use?

I will give you the file I use for Monthly MasterData, it provides the following:

  • YTD Indexes
  • YTG Indexes
  • Last Month Indexes
  • Current Month indexes
  • Month
  • Year
  • Next and Previous 3, 6, 12, 18 months rolling
  • Relative Month which gives the relative position of other months compared to the current month

To update all indexes dynamically, you need to each month: open the file, click on the Button to run the VBA macro which will update all the values of the attributes dynamically. That’s done. Save, go to PowerBI and just Refresh the file


And you, how do you handle your time analysis?



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s