Extracting date component with DAX
In this article, I’m 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 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?
Only Excel version 2016 will group the date field automatically.
Oooh, no. What about those users who’re not using version 2016? If you think about it, even if you’re 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 “The Fiscal Year articles”, 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”
Even if your company starts their financial cycle from January, I’m still very much reserved towards this automatic grouping feature in Excel 2016. Especially if you’re dealing with multiple facts table, this is going to be very confusing and difficult to manage.
So…..why?
Imaging that you’re 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 “ROWS” or “COLUMNS” 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.
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.
Checkout the clip below:
You will end up clutter the facts tables in Data Model Window and the view in Pivot Table Field List. I’d definitely encourage creating the A Date Table (AKA Date Dimension or Calendar Table) no matter what version of Excel that you’re using or which month that your company starts their financial year cycle. Don’t panic, I know what you’re thinking. You might be thinking “does it means every time when I’m creating data model I will have to re-create the whole Date Table??!!!!”
Worried about nothing, because this is going to be a one-off setup because I’m 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.
Extract Month_Num
To extract the month from the “Date” column, you will be using the DAX function Month(). This function will extract the month index from 1-12.
Extract Month
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.
Extract Quarter_Num
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.
Financial Quarter starts from January with ROUNDUP()
ROUNDUP() function will be used to extract the Quarter.
=”Q”&ROUNDUP(Month_Index/3,0)
| For month January | 1/3 = 0.33333 < after roundup the value will be 1, which is Q1 |
| For month February | 2/3=0.66666 < after roundup the value will be 1, which is still Q1 |
| For month March | 3/3=1.00000 < after roundup the value will be 1, which is still Q1 |
| For month April | 4/3=1.33333 < after roundup the value will be 2, which is Q2 |
| For month May | 5/3=2.66666 < after roundup the value will be 2, which is Q2 |
| And the list goes on until December. | |
*I did cover this part when I’m sharing about how to get Fiscal Quarter in Excel
Financial Quarter starts from April with SWITCH()
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.
Extract Fiscal_Quarter
This section we will be creating the text-based quarter. This is simpler than ever.
“FQ”&’date’[Quarter]
Join the text “FQ” with the Quarter number column.
