Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
294 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
The first formula retrieves the sales for one year ago, but it does so at the day, month,
quarter, and year levels. This makes SAMEPERIODLASTYEAR() an extremely powerful
function. The second formula works out the year-on-year change for any date. You can
achieve the same results using DATEADD().
STARTOFMONTH()
STARTOFMONTH() is the converse of ENDOFMONTH(). It returns the first day of the
month.
The example is a measure on the Order Details table.
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
STARTOFMONTH((Orders[OrderDate])))
Result
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 295
Analysis
The value for May 1998 is the same as that of May 1, 1998. The value for Q2 1998 is
the same as that of April 1998, not May 1998.
STARTOFQUARTER()
STARTOFQUARTER() returns the first day of the quarter.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Syntax
=CALCULATE(SUM(FactSales[SalesAmount]),STARTOFQUARTER(DimDate[Datekey]))
Result
Analysis
The result for 20071 is the same as the sales for January 1, 2007.
STARTOFYEAR()
STARTOFYEAR() returns the first day of the year. An optional second parameter lets
you specify a year-end date.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
296 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=CALCULATE(SUM(FactSales[SalesAmount]),STARTOFYEAR(DimDate[Datekey]))
Result
Analysis
The figures within each year reflect the sales for January 1. It might be fruitful to
compare the values for quarters (apart from first quarters) with the results from the last
formula.
TOTALMTD()
TOTALMTD() evaluates an expression over all the days of the month, up to and
including the current context day. The second parameter is the date column. You can
define a filter as an optional third parameter.
The example is a measure on the Order Details table.
Syntax
=TOTALMTD(SUM('Order Details'[Sales Amount]),Orders[OrderDate])
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 297
Result
Analysis
The totals for the year, the last quarter, and the last month are equal to the total for the
last day of the last month in the last quarter. The result is the same as the result of this
formula:
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESMTD(Orders[OrderDate]))
TOTALQTD()
TOTALQTD() returns all the days of the quarter, up to and including the current
context day when used with a calculation function. The first example has a third filter
parameter.
The examples are measures on the Order Details table.
298 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=TOTALQTD(SUM('Order Details'[Sales Amount]),Orders[OrderDate],ALL(Orders))
=TOTALQTD(SUM('Order Details'[Sales Amount]),Orders[OrderDate])
Result
Analysis
The two examples are subtly different. Take a look at the contrasting sales figures
for May 1998. The first formula does not reset the aggregation as it crosses monthly
boundaries, but the second one does. The first formula overrides the date filter context
as the OrderDate column is in the Orders table.
TOTALYTD()
TOTALYTD() returns all the days of the year, up to and including the current context
day when used with a calculation function. The example uses an optional filter argument
as the third parameter. A fourth parameter allows you to specify a year-end date.
The example is a measure on the Order Details table.
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 299
Syntax
=TOTALYTD(SUM('Order Details'[Sales Amount]),
Orders[OrderDate],ALL(Orders))
Result
Analysis
The total for Q2 1998 is that of Q1 and Q2. The total for 1998 is the same as the total
for Q2. The Grand Total is the same as that for 1998.
Well done! You have completed a long chapter! This is the last of the 35 time
intelligence functions.
This page intentionally left blank
Text Functions
Chapter 10
302 Practical PowerPivot & DAX Formulas for Excel 2010
T
his chapter deals with text (or string) manipulation. Most of the text functions
are the same as their Excel function equivalents; however, they accept text
or column parameters rather than worksheet cells or ranges. Many of these
text functions return text, and as such are usually more suitable for creating calculated
columns in PowerPivot tables rather than measures—text values can’t be added together
to give subtotals and totals in a pivot table (unless you do a COUNT()). Having said
that, if the text value can be converted into a number (for example the string “123”),
then it may be used as an additive measure. Usually, PowerPivot will implicitly do the
conversion, but you can always do it explicitly with the VALUE() function. Please note
that the FORMAT() text function also operates against numbers as well as against text.
C
Key concepts Comparing and finding strings, concatenating and parsing strings,
changing and formatting strings
C
Keywords CONCATENATE(), EXACT(), FIND(), FIXED(), FORMAT(),
LEFT(), LEN(), LOWER(), MID(), REPLACE(), REPT(), RIGHT(), SEARCH(),
SUBSTITUTE(), TRIM(), UPPER(), VALUE()
CONCATENATE() 1/2
Often, your source data is heavily normalized and names and addresses are parsed into
separate columns. This does not always make for easy viewing in a pivot table. Maybe
you want to put columns back together again. You can use the CONCATENATE() function
to reassemble text columns (it also works against numeric columns or a mix of text
and numeric). Slicing or filtering is probably easier if you have the full name of each
employee, rather than only the surname (you might have two or more employees sharing
a surname). The examples are calculated columns on the Northwind Employees table.
Syntax
=CONCATENATE('Employees'[LastName],'Employees'[FirstName])
=CONCATENATE('Employees'[LastName],", ",'Employees'[FirstName])
=CONCATENATE('Employees'[LastName],
CONCATENATE(", ",'Employees'[FirstName]))
='Employees'[LastName] & ", " & 'Employees'[FirstName]
Chapter 10: Text Functions 303
Result
Analysis
The first example works, although the third example is easier to read with a comma and
a space between the LastName and the FirstName. If you try them all, you’ll find that
the second example fails—CONCATENATE() accepts a maximum of two parameters.
If you wish to join three strings, you’ll have to use nested CONCATENATE() functions;
the third formula shows how. Alternatively, you can use the ampersand (&) operator
instead, as in the fourth formula. This method is not limited to two parameters, and
is often preferred over CONCATENATE(). The result shown is that from the third or
fourth formulas.
CONCATENATE() 2/2
The DAX text functions are often quite simple. Frequently, they are employed in
conjunction with other functions to produce useful output. The formula here allows
you to slice and filter your data in a pivot table on both customer country and region
at the same time (or just country if there is no region). The examples are Northwind
Customers table calculated columns.
Syntax
=CONCATENATE(CONCATENATE('Customers'[Country]," - "),
'Customers'[Region])
=IF(ISBLANK('Customers'[Region]),'Customers'[Country],
CONCATENATE(CONCATENATE('Customers'[Country]," - "),
'Customers'[Region]))