In this article, I\u2019m going to go through with you how to extract date component out of the date column. You are going to extract text and number based calculated column using DAX function.<\/p>\n\n\n\n
So, many of you may curious, why do you need to create all these calculated columns while Pivot Table in Excel (only version 2016) will automatically group the date field into year, Quarter and month while the Date field is being drag into the Pivot table field area?<\/p>\n\n\n\n
Only Excel version 2016 will group the date field automatically.<\/em><\/p>\n\n\n\n Oooh, no. What about those users who\u2019re not using version 2016? If you think about it, even if you\u2019re the user in Excel version 2016, the grouping feature in the Pivot table field might not be as useful. The grouping feature that we just talked about, will only group the quarter based on the conventional type of grouping. If you have been following \u201cThe Fiscal Year articles\u201d, you will have learned that mostly company does not start their financial cycle from January. Which means, you will still have to create this Data table if you are going to create a report\u201d<\/p>\n\n\n\n Even if your company starts their financial cycle from January, I\u2019m still very much reserved towards this automatic grouping feature in Excel 2016. Especially if you\u2019re dealing with multiple facts table, this is going to be very confusing and difficult to manage.<\/p>\n\n\n\n So\u2026..why?<\/p>\n\n\n\n Imaging that you\u2019re having 3 facts table with all their fields listed in the pivot table fields, and every time when you drag the date field into the \u201cROWS\u201d or \u201cCOLUMNS\u201d field in the pivot table builder area, it will automatically group the date into 3 fields. With that, you are adding more fields and columns to the column. And it is going to harder to manager, per se.<\/p>\n\n\n\n There is one you should know. When the automatic grouping happened, Pivot Table will physically add new columns into the table that is being stored in Data Model window. The more columns you have in the Data Model windows, the harder it is for you to manage.<\/p>\n\n\n\n Checkout the clip below:<\/p>\n\n\n\n You will end up clutter the facts tables in Data Model Window and the view in Pivot Table Field List. I\u2019d definitely encourage creating the A Date Table (AKA Date Dimension or Calendar Table<\/em>) no matter what version of Excel that you\u2019re using or which month that your company starts their financial year cycle. Don\u2019t panic, I know what you\u2019re thinking. You might be thinking \u201cdoes it means every time when I\u2019m creating data model I will have to re-create the whole Date Table??!!!!\u201d<\/p>\n\n\n\n Worried about nothing, because this is going to be a one-off setup because I\u2019m going to show you after setting up the Date Table, you can save the file as template file. With that, it is going to save you tons of time and making your life so so so easy, I promise.<\/p>\n\n\n\n To extract the month from the \u201cDate\u201d column, you will be using the DAX function Month(). This function will extract the month index from 1-12.<\/p>\n\n\n\n We are going to extract the text based month using DAX function Format(). This function will convert a value to text according to specified format code supplied by user.<\/p>\n\n\n\n Well, extracting quarter number can be a lil tricky. This is if your financial quarter does not begin from January. I will be showing you a few different ways to extract the Quarter.<\/p>\n\n\n\n Financial Quarter starts from January with ROUNDUP()<\/strong><\/p>\n\n\n\n ROUNDUP() function will be used to extract the Quarter.<\/p>\n\n\n\n =\u201dQ\u201d&ROUNDUP(Month_Index\/3,0)<\/p>\n\n\n\n *I did cover this part when I\u2019m sharing about how to get Fiscal Quarter in Excel<\/em><\/p>\n\n\n\n Financial Quarter starts from April with SWITCH()<\/strong><\/p>\n\n\n\n Switch() function evaluates an expression against a list of values and returns one of multiple possible result expressions. It is very much similar with Excel function Choose(). Take a look at the clip below and see how I use Switch() function to get the fiscal quarter. If you manage to grasp how this Switch() functions works, you basically can extract the quarter whichever month the financial cycle begins.<\/p>\n\n\n\n This section we will be creating the text-based quarter. This is simpler than ever.<\/p>\n\n\n\n \u201cFQ\u201d&\u2019date\u2019[Quarter]<\/p>\n\n\n\n Join the text \u201cFQ\u201d with the Quarter number column.<\/p>\n","protected":false},"excerpt":{"rendered":" Extracting date component with DAX In this article, I\u2019m going to go through with you how to extract date component out of the date column. You are going to extract text and number based calculated column using DAX function. So, many of you may curious, why do you need to create all these calculated columns […]<\/p>\n","protected":false},"author":4,"featured_media":327,"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-921","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\/921","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=921"}],"version-history":[{"count":2,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/921\/revisions"}],"predecessor-version":[{"id":1848,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/921\/revisions\/1848"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/327"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=921"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=921"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=921"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Extract Month_Num<\/h2>\n\n\n\n
Extract Month<\/h2>\n\n\n\n
Extract Quarter_Num<\/h2>\n\n\n\n
For month January<\/td> 1\/3 = 0.33333 < after roundup the value will be 1, which is Q1<\/td><\/tr> For month February<\/td> 2\/3=0.66666 < after roundup the value will be 1, which is still Q1<\/td><\/tr> For month March<\/td> 3\/3=1.00000 < after roundup the value will be 1, which is still Q1<\/td><\/tr> For month April<\/td> 4\/3=1.33333 < after roundup the value will be 2, which is Q2<\/td><\/tr> For month May<\/td> 5\/3=2.66666 < after roundup the value will be 2, which is Q2<\/td><\/tr> And the list goes on until December.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Extract Fiscal_Quarter<\/h2>\n\n\n\n