If you are serious about using PowerPivot, you need to learn a little about the DAX language at the very least to create calculated columns. Last week I introduced the basic syntax of DAX. This week, as promised, I will help you get started with DAX functions. Am I going to teach you everything you need to know about all of the DAX functions? No, that would be too ambitious for a single blog entry. However, I do want to guide you down the path of some of the most common DAX functions that you may find useful when building calculated columns.
First, you should know that DAX functions can be grouped into 8 groups (some people say 7 depending on whether they lump the Date and Time functions together with the Time Intelligence functions which are significantly more complex) as shown here:
- Date and Time
- Math and Trigonometry
- Time Intelligence
The only group I explore in any detail here will be the Date and Time functions. Why only look at one out of 8 groups? Well, first off, this group holds common functions used to manipulate dates and time, extract parts of dates and time, and calculate some date offsets. Second, after showing how easy these initial functions are to understand, I will provide you with links so you can explore some of the other function groups on your own.
So for the date and time functions, I will give you a basic list of the functions and short description of what they do. I may even add a few comments about some of the functions that you may not have seen before, but most stand on their own because variations of them have been around for years. So without further delay, let’s look at the functions that make up the Date and Time group.
|This function returns a date value from integer arguments for the year, month and day.|
|=DATEVALUE(<date_text>)||This function takes a date formatted as a string such as “10/29/2011” and converts it to a datetime value. This function is useful when you data source has dates stored as strings rather than date fields.|
|=DAY(<date>)||Returns an integer representing the day of the month from the date argument which can be a column or date string.|
|=EDATE(<start_date>,<months>)||Returns a date the indicated number of months in the future or past from the current date. If the date would be after the last day in the destination month, it automatically reverts to the last day in the month. For example both EDATE(“1/30/2011”,1) and EDATE(“1/31/2011”,1) would return “2/28/2011”.|
|=EOMONTH(<start_date>,<months>)||This function differs from EDATE in that it always returns the date of the last day in the month that is <months> before or after the <start_date>. For example, EOMONTH(“1/16/2011”,1) will return “2/28/2011”. This is a very interesting function if you need the last day of the month for billing purposes.|
|=HOUR<datetime>)||Returns the hour number (using military time of 0 (12:00 AM) to 23 (11:00 PM)) of the datetime argument.|
|=MINUTE(<datetime>)||Returns the minutes number (0 to 59) of the datetime argument.|
|=MONTH(<datetime>)||Returns the month number (1=January to 2=December) for the datetime argument.|
|=NOW()||This functions has no arguments, but returns the current date and time. Note that using this function will result in the value being updated every time the formula is refreshed which may not be what you want. However you can decimal numbers to the result of this function to calculate a date offset. For example, =NOW()+5.0 returns a datetime value 5 days from now.|
|=SECOND(<datetime>)||Returns the seconds number (0 to 59) of the datetime argument.|
|=TIME(<hour>,< minute>, <second>)||Returns a time value in which the time is calculated from the three arguments for hour, minute, and second.|
|=TIMEVALUE(<time_text>)||Converts a time supplied in text format to a time value in datetime format such as =TIMEVALUE(“16:30:00”) for 4:30 PM.|
|=TODAY()||This function is similar to =NOW() but returns only the date, not the current date and time. All times are represented as 12:00 AM.|
|=WEEKDAY(<datetime>)||Returns a number from 1 to 7 that identifies the day of the week for the datetime argument. Unless you have changed your computer’s settings, a value of 1 corresponds to Sunday. Given the week day number of a given date, it is possible to create an expression to calculate the date for the Monday of that week. Think a bit about it. You may need to also look at the =IF() function to do this, but nesting functions is what DAX is all about.|
|=WEEKNUM(<date>,<return_type>)||This function returns a number that identifies the week of the current year. The key to understanding this function is that it identifies the week that contains January 1 as the first week of the year even if that week begins with a date in the prior year.
The <return_type> value of 1 (the default) begins the week on Sunday. The <return_type> value of 2 begins the week on Monday.
|=YEAR(<date>)||Returns a four digit integer representing the year for the date argument.|
|=YEARFRAC(<start_date>, <end_date>, <basis>)||Calculates a fraction representing the portion of the year between the two dates. The values of <basis> determines the number of days in the year based on the following values:
0 US (NASD) 30/360
4 European 30/360
Knowing the basis of the calculation, you could multiply the resulting fraction to get the number of days between the two dates.
You probably notices that some of these functions are very similar to functions you already know in Excel, SQL or other development language. However, several such as =EMONTH(), =EOMONTH, and =YEARFRAC() are new but are fairly simply to understand. Some of these functions could help you build a date dimension table based on dates in a fact table if such a table does not already exist. We’ll get to the details of how to do that later after we learn a few more functions so be patient.
Details about the Date and Time function can be found at: http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-date-and-time-functions.aspx as well as http://technet.microsoft.com/en-us/library/ee634786(SQL.110).aspx
Now that I have shown you how easy it is to figure out what some of the DAX functions do, I’m going to give you a homework assignment. In the following list, I have included links for several other common function groups used in creating calculated columns with PowerPivot for Excel.
|For Information Functions, go to:||http://technet.microsoft.com/en-us/library/ee634552(SQL.110).aspx|
|For Logical functions go to:||http://technet.microsoft.com/en-us/library/ee634365(SQL.110).aspx|
|For Math and Trigonometry functions go to:||http://technet.microsoft.com/en-us/library/ee634241(SQL.110).aspx|
|For Statistical functions go to:||http://technet.microsoft.com/en-us/library/ee634822(SQL.110).aspx|
For the next several weeks, there are some other topics I want to cover while you spend some time exploring and working with these functions before moving on to some of the more complex DAX functions and how to begin nesting them to produce interesting results. One last point that deserves repeating, while the Time Intelligence functions are actually related to the Date and Time functions, they are significantly more complex to understand so we group them separately and will examine them at a later time after exploring some of the other function groups.