In the previous article, we saw how to create custom sorting order using a separate month table.Now we will see how a new type of table called Time Table can be an even better solution. Estimated reading time 7 min
The objective is to sort months in the right order and NOT in alphabetical order. We saw two methods of doing this: Custom List based sort order and Related Months table with an ordering column.
In this article , another related concept is explained. The concept is simple. We need to perform different type of analysis based upon dates and time.
The transactional files will usually have a specific date time as a timestamp to indicate the time at which something happened.
While analyzing data, however, we need to look at the time and date portion at a higher level. Higher level could be days, months, weeks, quarters etc.
In Pivot table, this level of grouping was done by Pivot Table itself. However in Power Pivot, we have to manage this grouping ourselves The Group… option is missing in Power Pivot.
Usually, any grouping requirement could be satisfied by adding a calculated column in the raw data which contains the requisite function like Month() or Year(). However that will make the data size explode and reduce performance. Too many calculations will need to be done for each row.
Therefore, a new approach is needed. The idea is to use a table containing ALL the dates in the analysis period. The first column is these dates and additional columns contain all kinds of useful grouping information. Here is how a Time Table looks. You can create your own or use it from various samples provided by Microsoft. Time tables are also available for download on Azure Marketplace.
Download files and follow along
This contains no data in the worksheet. Go to Power Pivot – Data Model to view the data.
Transactions.xlsx contains data with a date column and amount column. For simplifying the example, all other transaction details are omitted.
The time table covers every day from 1st January 2007 to 31st January 2015. First column contains date and rest of the columns are either grouping information or sorting information.
In this article, we just need to get the Month column and sort it on MonthNumber column for each row in the transaction table.
The Quarter column is interesting. It is based upon financial year starting in April instead of January. We could also have had a simple column containing zero or one called “working day”. But we will discuss Time Table usage in another article.
Let us focus on getting the Month for each transaction and sorting it in the correct order.
It is easier to show the process as a video. Open the sample file – go to the data model in Power Pivot and follow the video.
View the video in full screen mode at HD resolution (720 p) if possible.
In future articles, I will cover the concept of Power Pivot in greater detail.