Excel Unplugged

Extending the Date Table – Part 1

Banner Extending the Date Table

Let’s look at some crucial tools for creating polished dynamic dashboards. We always extend the date table with three important columns: YM Index, Running 13 and Current Month. Part one will cover the basics – creating the columns in Excel. In part 2, we’ll see how it’s done in DAX and M.

The sorting problem

We often add a column of custom formatted date to our date table. Here at Excel Unplugged, we prefer using dates in the form of »Jan 17«, as seen in the YM column on the image below.

Extending the Date Table - Part 1, Dates
YM column

This date format looks neat and clear in all kinds of charts and business reports. The problem is, it doesn’t sort correctly as it’s not recognized as date, of course. Let’s test that. We insert a Pivot Table (and add it to Data model as well, so we can fix it using Power Pivot).

Extending the Date Table - Part 1
Creating a pivot table

When using the YM column (that is really just text) as rows we get the following pivot table.

Extending the Date Table - Part 1, Unsorted dates
YM column not sorting correctly

This is why we create another custom column and sort it by this new column. Let’s see how that works.

The most Important Column – Year Month Index

We’ll call this powerful new column Year Month Index. We create it using the following formula.

= YEAR(A2)*12 + MONTH(A2)

Extending the Date Table - Part 1
YM Index column

We already added our table to Data Model. We open Power Pivot, select the YM column and select the Sort by Column. Then we set the column to sort by YM Index column, as seen on image below.

Extending the Date Table - Part 1, Sort by
Setting Sort By YM Index column

After refreshing the pivot table, the data now sorts correctly.

Extending the Date Table - Part 1, Correct sorting
YM column sorting correctly!

How easy! This is a very powerful trick that we can use both in Excel or Power BI to get our neatly formatted dates to sort correctly.

Running 13 Column

Another very useful column we can create is called Running 13 or Rolling 13. We often have large datasets spanning over multiple years, but are only interested in the last year of data. For example, the following plot would look much cleaner if we only displayed the last 13 months.

Extending the Date Table - Part 1, Data
Large table of data

Let’s solve this by creating the Running 13 column. We create new column Running 13 and use the following formula.

=IF(AND((YEAR(TODAY())*12+MONTH(TODAY()))-G5>=0;(YEAR(TODAY())*12+MONTH(TODAY()))-G5<13);1;0)

The formula prints out 0 for month that fall our of our last 13 months window and 1 for the months within the window.

Extending the Date Table - Part 1, Formula
Rolling 13 formula

We now insert a slicer for our pivot table by selecting Insert > Slicer >Rolling 13. On the slicer, we select value 1. The table is filtered and only last 13 months are left.

Extending the Date Table - Part 1, Running 13
Rolling 13

Since today is November 2021, we are now only displaying Nov 2021 and previous 13 months, including November 2020. So handy, we can even compare last November with current one! Imagine how useful this column gets when you’re dealing with business reports in dashboards. Viewing the last year of your business statement has never been easier!

Current Month Column

Another powerful trick we can use is Current Month column. Again another column that becomes priceless when viewing our data in various dashboards. It allows us to slice our data to the current month, whatever that may be. Here’s how to create it.

Create a new column Current Month and type in the following formula.

=IF(YEAR(TODAY())*12+MONTH(TODAY())=G12;”CURRENT MONTH”; F12)

Current Month formula

We now insert a slicer for our pivot table by selecting Insert > Slicer > Current Month. On the slicer, we select value CURRENT MONTH.

Extending the Date Table - Part 1, Current Month
Current Month

The data and plot are filtered to the current month, Nov 21. Just brilliant!

All three tricks are priceless when building beautiful dynamic dashboards in Excel or Power BI. These were the core fundamentals, in part two of this article we’ll take it to Power Query and see how to do the same thing in DAX and M. Stay tuned!

Watch the tutorial

Listen to all the tricks in the following video:

Extending Date Table Video Tutorial

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out our brand new R Academy!

Or, read related posts: