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
Again the Monthly Master data could come handy, if we build some columns Year and Month as dates attributes in the Monthly MasterData
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
- Write formulas: this article will show you how to build it using formulas in the Query editor
- Download an existing table: This great article by Rob Collie completes my article and will show you how to get the DateStream table from Boyan Penev
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
- 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?