Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
244 Practical PowerPivot & DAX Formulas for Excel 2010
F
or the purpose of clarity, the Date & Time functions in DAX have been divided
into two chapters. This is the first of those two chapters and deals with the more
basic date and time functions. The next chapter is devoted to a group of Date
& Time functions that are often referred to as time intelligence functions. The basic
functions are often simpler and more forgiving. The time intelligence functions usually
require that you have put some thought into how your dates are stored in the PowerPivot
model. The emphasis in this chapter is on parsing, manipulating, and returning dates.
The basic functions generally return dates and times. The time intelligence functions
generally return measures or values associated with dates and times.
Please note that the display of dates and times is determined by your regional
settings in Control Panel and the choices you make in the Formatting group of the
PowerPivot Home ribbon. Dates and times displayed in a pivot table can also be
customized to your liking. PowerPivot uses a special date data type, unlike Excel itself,
which conforms to the Gregorian calendar.
Also, please be aware that there are different incarnations of the Northwind database.
They often contain slightly different dates, and your results (if they use the Northwind
Orders table and OrderDate column) may not always match the results shown in
screenshots in this chapter.
C
Key concepts Converting numbers and text to dates, parsing dates,
manipulating dates, returning the current date and time
C
Keywords DATE(), DATEVALUE(), DAY(), EDATE(), EOMONTH(), HOUR(),
MINUTE(), MONTH(), NOW(), SECOND(), TIME(), TIMEVALUE(), TODAY(),
WEEKDAY(), WEEKNUM(), YEAR(), YEARFRAC()
DATE()
DATE() accepts a comma-separated list of three numbers (year, month, day) and
returns a date, with a data type of date. You can also have three strings, provided the
strings can be converted into appropriate numbers.
The examples are for calculated columns in any table.
Syntax
=DATE(2001,12,31)
=DATE(01,12,31)
Chapter 8: Date & Time Functions 1/2: Basic Functions 245
Result
Analysis
The result shown is from the first formula. If you recall, you can control the formatting
of dates and times. To do so, use the Format drop-down menu in the Formatting group
of the Home ribbon in PowerPivot. Please be careful with DATE(); the second example
results in 1901, not 2001. It’s probably a good idea to display four-digit years. DATE()
accepts numbers as inputs. If your input is one string, use DATEVALUE() instead, as
follows:
=DATEVALUE("2001/12/31")
However, DATE() will work with three separate strings (it implicitly converts them
to numbers), as here:
=DATE("2001","12","31")
DATEVALUE() is covered next in this chapter.
DATEVALUE()
Like DATE(), DATEVALUE() returns a date as a date data type. Unlike DATE(), it
takes a string or a text column as its single parameter. The string or text column must
conform to certain rules—the following example fails.
The examples are for calculated columns in any table.
Syntax
=DATEVALUE("2001/12/31")
=DATEVALUE("20011231")
246 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
The first formula is successful and the result is shown in the screenshot. The second one
gives an error. You can use Text functions to manipulate the string into a valid format
for DATEVALUE(), or alternatively, try Text functions and the DATE() function:
=DATE(LEFT("20011231",4),MID("20011231",5,2),RIGHT("20011231",2))
LEFT(), MID(), and RIGHT() are Text functions and are covered in Chapter 10.
DAY()
DAY() returns the day of the month. This function and some of the other Date &
Time functions are useful for creating date drill-down columns in your pivot tables.
The example is for a calculated column in the Orders table.
Syntax
=DAY(Orders[OrderDate])
Result
Chapter 8: Date & Time Functions 1/2: Basic Functions 247
Analysis
In this formula, DAY() is parsing a regular column of data type date. DAY() can also
accept a text parameter:
=DAY("2001/12/31")
EDATE()
If you need a date that is so many months in the future (or the past), EDATE() is the
function to use. For dates in the past, make the second parameter negative.
The example is for a calculated column in the Orders table.
Syntax
=EDATE(Orders[OrderDate],6)
Result
Analysis
Your business rule may be that orders must ship within six months of the order date.
You might use this formula and compare it to the actual ShippedDate column in the
Orders table. That way, you can see that you shipped on time (or not!).
EOMONTH()
This EOMONTH() function is a handy alternative to EDATE(). Not only does it jump
into the future (or the past), but it then returns the last day of the month in that future
(or past) date. This is an ideal function for working out maturity dates.
The example is for a calculated column in the Orders table.
248 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=EOMONTH(Orders[OrderDate],6)
Result
Analysis
If you compare the result to that of the EDATE() one in the previous example, you’ll see
that EOMONTH() is giving the month-end dates—EO means EndOf.
HOUR()
HOUR() simply returns a number between 0 and 23. It accepts a date column, a time
value, or a string as its single input parameter.
The first example is for a calculated column in any table. The second example is for a
calculated column in the Orders table.
Syntax
=HOUR(NOW())
=HOUR(Orders[OrderDate])
Chapter 8: Date & Time Functions 1/2: Basic Functions 249
Result
Analysis
The result shown is from the first example. Your result, of course, may be different.
NOW(), which is described later in this chapter, returns the current date and time.
The second example returns zero—that’s because the OrderDate column has no time
component, so it defaults to midnight. HOUR() can also work on strings:
=HOUR("2001/12/31 5:19 PM")
MINUTE()
As HOUR() gives the hour, MINUTE(), unsurprisingly, gives the minute, between 0 and 59.
The example is for a calculated column in any table.
Syntax
=MINUTE(NOW())
Result
250 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
Again, your result may vary from the screenshot here. MINUTE() can also work with a
string parameter:
=MINUTE("2001/12/31 5:19 PM")
MONTH()
MONTH() returns the month from a date—as a number, between 1 and 12, not as a
month name.
The example is for a calculated column in the Orders table.
Syntax
=MONTH(Orders[OrderDate])
Result
Analysis
MONTH() also operates on a suitably formatted string:
=MONTH("2001/12/31 5:19 PM")
=MONTH("2001/12/31")
Chapter 8: Date & Time Functions 1/2: Basic Functions 251
The calculated column that contains the returned month number is often added to
Column Labels or Row Labels in a pivot table. It’s frequently combined with a year
column and a quarter column to give drill-down on dates. There is, however, a potential
drawback—the months appear as numbers rather than month names. Many users prefer
to see the month names. Unfortunately, there is no DAX function in this first release of
PowerPivot to give you the month name directly. There are at least two solutions, if the
month name is not already part of your source data. One, you can write your own DAX
formula to generate the month name. It might begin something like the following:
=IF(MONTH(Orders[OrderDate])=1,"January",
IF(MONTH(Orders[OrderDate])=2,"February",
and so on. Alternatively, you could do this:
=IF(MONTH(Orders[OrderDate])=1,"Jan",
IF(MONTH(Orders[OrderDate])=2,"Feb",
and so on.
Two, you can write a query when you import the data, if it’s from a suitable source.
For a SQL Server source, you might try one of these:
select datename(mm,orderdate) as MonthName from orders
select left(datename(mm,orderdate),3) as MonthName from orders
This, in turn, raises another interesting problem. If you add the month name to Row
Labels or Column labels in a pivot table, the month names are sorted alphabetically
(April, August, and so on). Fortunately, there is a GUI solution. First, right-click on
a month name in the pivot table. Choose Sort, More Sort Options to open the Sort
dialog. In this dialog, click More Options to open the More Sort Options dialog. In
this second dialog, under AutoSort, turn off Sort Automatically Every Time the Report
Is Updated. Then, from the First Key sort order drop-down, choose the entry beginning
Jan or January (whichever is appropriate). Click OK twice to exit the two dialogs.
Right-click once again on the month name in the pivot table, and choose Sort, followed
by Sort A to Z.
NOW()
NOW() returns the current date and time. This is in contrast with TODAY(), which only
returns the date.
The example is for a calculated column in any table.
252 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=NOW()
Result
Analysis
If you refresh the data in the table containing the column, the return value of NOW()
will be updated. A refresh causes a recalculation. The same applies if you are in Manual
Calculation mode and you choose Calculate Now. The TODAY() function appears later
in this chapter.
There Is No QUARTER()
Partly in order to conform to existing Excel functions, there is no QUARTER() function
in DAX. This example presents just one of a few possible solutions to extract the
quarter from a date.
The example is for a calculated column in the Orders table.
Syntax
=IF(MONTH(Orders[OrderDate])<=3,1,IF(MONTH(Orders[OrderDate])<=6,2,
IF(MONTH(Orders[OrderDate])<=9,3,4)))
Chapter 8: Date & Time Functions 1/2: Basic Functions 253
Result
Analysis
You might also try the CONCATENATE() function to prepend the letter “Q” to the
quarter number. CONCATENATE() is a Text function and is discussed in Chapter 10. Or
you might want to try this approach:
=IF(MONTH(Orders[OrderDate])<=3,"Q1",
IF(MONTH(Orders[OrderDate])<=6,"Q2",IF(MONTH(Orders[OrderDate])<=9,"Q3","
Q4")))
As an alternative to a DAX formula, you may want to return the quarter as part of
an import query. Here are two possible SQL Server solutions:
select datepart(qq,orderdate) as MonthName from orders
select 'Q' + cast(datepart(qq,orderdate) as char) as MonthName
from orders
SECOND()
SECOND() operates on date columns or on strings to give the second, from 0 to 59.
The example is for a calculated column in any table.
Syntax
=SECOND("2001/12/31 5:19:45 PM")