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.
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.
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).
When using the YM column (that is really just text) as rows we get the following pivot table.
This is why we create another custom column and sort it by this new column. Let’s see how that works.
We’ll call this powerful new column Year Month Index. We create it using the following formula.
= YEAR(A2)*12 + MONTH(A2)
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.
After refreshing the pivot table, the data now sorts 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.
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.
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.
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.
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!
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)
We now insert a slicer for our pivot table by selecting Insert > Slicer > Current Month. On the slicer, we select value 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!
Listen to all the tricks in the following video:
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:
We’re continuing our quest for beautiful and dynamic ...
I guess every data professional that has ever taken on the ...