No! Don’t you dare say no to calendar table
Create Calendar Table using DAX function – Power Pivot
If you are a pivot table long time user, you know you will be able to group the date field using the grouping features, extracting the Year, Quarters, Months and days out of the date field.
If you’re not familiar with Pivot Table and have no idea Pivot Table is capable of doing so. You can achieve this result by placing the date field to either the Row/Column label field. Once all the date has successfully displayed in the Pivot Table area, right click at any of the date in the Pivot Table, you should be able to locate the grouping option.
But if you had your data being stored in the Data Model, this feature will not be available (for version 2010 and 2013. If you’re using Power Pivot in version 2016, all the dates will be grouped automatically.
Now back to Power Pivot. If you’re a Power Pivot user of version 2010 and 2013, you will have to create a calendar table. Like it or not, even you’re in version 2016, it is always better to create a calendar table even though the date field is grouped automatically.
Simply because the calendar table that you’re about to create is like a lookup table, that contains columns like MonthNumber, Quarter, WeekNum and all other date related column (anything, you name it). The best part is, it is not going to contains tons of rows. With this lookup table, you can link it up with many other facts / data table. it is not just that, this calendar is also reusable. Later on, I will also walk you through, how to make it reusable and sharable.
You seriously need to create calendar table
Version 2010 & 2013
Let’s say, if you insist not to create the calendar table ( I seriously don’t know what else you gonna do in your life), especially if you’re the user from Power Pivot version 2010 and 2013 and thinking you can simply add helper column in the facts table since you can “add column” in it. Can you imagine how many columns you have to add per facts table, what about if you’re having multiple facts table. What you’re doing is simply adding more and more columns to those tables, and every helper column you added into those facts table, at the meantime you’re generating more rows and columns into the table. Plus, those helper columns you can hardly reuse them, almost impossible.
Oohhhh, no no no! you must be thinking you’re gonna skip calendar table. no no no no no !.Even if you’re in the latest version (oh well while I’m writing this, latest version is 2016. Just in case you’re reading from the future) and enjoying all the most advanced perks being the latest version user, so you get to skip this table. In fact, there are many limitations even grouping of date periods is not an issue to you. While the grouping is automatic, it actually has no different as you are inserting helper column. Every time when the grouping form automatically, it inserts physical column into the data model which is again expanding your facts table unnecessarily. Besides, it also adding in tons of rows into your facts table pointlessly.
Check out the short clip below:
So, imaging if you have a few facts table in the data model window and this happened every single time while you drop the date field into the Row or Column field? Too much to handle I would say. So, creating calendar will only do you good, no harm.
Custom financial year, quarter and month
So, for all Power Pivot user, whether you’re using 2010 or 2016 this is important. If you’re company’s financial year is not the conventional type which is begins from the month of January, this calendar is even more useful. Because I’m going to talk about how you can make it reusable and share it with your mates. If you have never been a big fan of Excel Template file, after this imma make you felt so in love with it.
You’re are going to create a template file and every time when you open it, it automatically comes with calendar table which you will never ever ever ever have to re-create any calendar table, if possible, anymore! Which I truly believe routine is a waste of time. With this, your job is going to be as simple as right click and refresh. Unless there are some changes in the calendar settings, else, you can almost just leave the calendar table alone. (forever in peace, amen)