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.<\/p>\n\n\n\n
If you\u2019re 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.<\/p>\n\n\n\n
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\u2019re using Power Pivot in version 2016, all the dates will be grouped automatically.<\/p>\n\n\n\n
Now back to Power Pivot. If you\u2019re a Power Pivot user of version 2010 and 2013, you will have to create a calendar table. Like it or not, even you\u2019re in version 2016, it is always better to create a calendar table even though the date field is grouped automatically.<\/p>\n\n\n\n
Simply because the calendar table that you\u2019re 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<\/strong>. Later on, I will also walk you through, how to make it reusable and sharable.<\/p>\n\n\n\n Version 2010 & 2013<\/strong><\/p>\n\n\n\n Let\u2019s say, if you insist not<\/strong> to create the calendar table ( I seriously don\u2019t know what else you gonna do in your life), especially if you\u2019re the user from Power Pivot version 2010 and 2013 and thinking you can simply add helper column in the facts table since you can \u201cadd column\u201d in it. Can you imagine how many columns you have to add per facts table, what about if you\u2019re having multiple facts table. What you\u2019re 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\u2019re generating more rows and columns into the table. Plus, those helper columns you can hardly reuse them, almost impossible.<\/p>\n\n\n\n Version 2016<\/strong><\/p>\n\n\n\n Oohhhh, no no no! you must be thinking you\u2019re gonna skip calendar table. no no no no no !.Even if you\u2019re in the latest version (oh well while I\u2019m writing this, latest version is 2016. Just in case you\u2019re 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.<\/p>\n\n\n\n Check out the short clip below:<\/p>\n\n\n\n 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.<\/p>\n\n\n\n So, for all Power Pivot user, whether you\u2019re using 2010 or 2016 this is important. If you\u2019re company\u2019s financial year is not the conventional type which is begins from the month of January, this calendar is even more useful. Because I\u2019m 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.<\/p>\n\n\n\n You\u2019re 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)<\/p>\n","protected":false},"excerpt":{"rendered":" No! Don\u2019t you dare say no to calendar table Create Calendar Table using DAX function \u2013 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 […]<\/p>\n","protected":false},"author":4,"featured_media":244,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-925","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/925","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/comments?post=925"}],"version-history":[{"count":2,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/925\/revisions"}],"predecessor-version":[{"id":1845,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/925\/revisions\/1845"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/244"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=925"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=925"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=925"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}You seriously need to create calendar table<\/h2>\n\n\n\n
Custom financial year, quarter and month<\/h2>\n\n\n\n