Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
264 Practical PowerPivot & DAX Formulas for Excel 2010
Here are the steps to import suitable tables from ContosoRetailDW:
1. Either from a new PowerPivot model, or from an existing Northwind PowerPivot
model, connect to SQL Server and to the ContosoRetailDW database.
2. Import two tables: DimDate and FactSales.
3. On the Design ribbon, click Manage Relationships and verify that FactSales is
related to DimDate through the DateKey (called Datekey in DimDate) column.
CLOSINGBALANCEMONTH()
Please note that the Year, Quarter, Month, and OrderDate columns from the Orders
table have been added to the Row Labels in the pivot table. The Quantity column from
Order Details has been added to the Values drop-zone before creating the measures
for our examples. There are slicers on Year and Category. CLOSINGBALANCEMONTH()
returns the last value for the current month context when combined with a calculation
function. Often, this will be the value for the last day of the current month. In general,
all of the CLOSINGBALANCE() and OPENINGBALANCE() family of functions accept
an expression as the first parameter and return a scalar value. You can also have an
optional third parameter—a SetFilter parameter, as shown in the second example.
The examples are measures. The measures have been added to the Order Details
table. Unless otherwise explicitly stated, the functions and formulas in this chapter use
the Northwind PowerPivot model. As there are various incarnations of Northwind,
your figures may differ from those shown here.
Syntax
=CLOSINGBALANCEMONTH(SUM('Order Details'[Quantity]),Orders[OrderDate])
=CLOSINGBALANCEMONTH(SUM('Order Details'[Quantity]),Orders[OrderDate],
ALL(Products[Category]))
=IF(COUNTROWS(VALUES(Orders[OrderDate])) > 1,
CLOSINGBALANCEMONTH(SUM('Order Details'[Quantity]),
Orders[OrderDate],ALL(Products[Category])))
=CALCULATE(SUM('Order Details'[Quantity]),LASTDATE(Orders[OrderDate]))
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 265
Result
Analysis
The time intelligence functions require that the date column has a date data type.
Many of the functions accept a SetFilter parameter, as the second and third
examples show. The first formula results in 42 for 1998, Q2 of 1998, month 5 of 1998,
and May 6. This is the closing balance, at the month level, for all of those periods. The
figure for 1996 is 35. If you expand, you will see that the quantity sold on December 31
of that year is 35. The second example ignores the filter set in the Category slicer. The
third example suppresses the result at the daily level—VALUES() always has more than
one row at aggregate levels. The fourth example uses LASTDATE() to produce a similar
result, but not at the daily level. LASTDATE() is covered later in this chapter.
CLOSINGBALANCEQUARTER()
CLOSINGBALANCEQUARTER() returns the last value for the current quarter context if
a calculation formula is also used.
The example is a measure on the Order Details table.
266 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=CLOSINGBALANCEQUARTER(SUM('Order Details'[Quantity]),Orders[OrderDate])
Result
Analysis
Superficially, these results are similar to those of the previous function with the
Category slicer overridden. However, the figure for month 4 of 1998 has disappeared.
This function is only going to show the values for the last month in a quarter—based
on the last day of the period. The previous function showed values for all months. If no
value exists, the function will return a blank.
CLOSINGBALANCEYEAR()
CLOSINGBALANCEYEAR() returns the last value for the current year context when
used with a calculation. With this function, and OPENINGBALANCEYEAR(), there is an
optional fourth parameter for the year-end date—the default is December 31. This is
useful if you are not working with regular calendar years.
The example is a measure on the Order Details table.
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 267
Syntax
=CLOSINGBALANCEYEAR(SUM('Order Details'[Quantity]),Orders[OrderDate])
Result
Analysis
This time, the value for Q1 of 1998 has gone—it is not the last quarter in that
year. Incidentally, CLOSINGBALANCEYEAR() on a year returns the same figure as
OPENINGBALANCEYEAR() from the next year. OPENINGBALANCEYEAR() is covered
later in this chapter.
DATEADD()
DATEADD() allows you to navigate backward or forward in time. It’s useful for making
comparisons across time.
268 Practical PowerPivot & DAX Formulas for Excel 2010
The first example is a calculated column on the Orders table. The second, third, and
fourth formulas are measures and use ContosoRetailDW. The resulting screenshot
is for the second, third, and fourth examples. Before creating the first measure for
ContosoRetailDW, add SalesAmount from FactSales to the Values drop-zone.
Also, place CalendarYear, CalendarQuarter, CalendarMonth, and Datekey from
DimDate on the Row Labels of the pivot table. When you create the measures, place
them in the FactSales table. If you tried the previous function, you may want to remove
the slicers from that example—otherwise, you may receive a “Relationship needed”
warning.
Syntax
=DATEADD(Orders[OrderDate],-1,YEAR)
=CALCULATE(SUM(FactSales[SalesAmount]),
DATEADD(DimDate[Datekey],-1,YEAR))
=SUM(FactSales[SalesAmount])/CALCULATE(SUM(FactSales[SalesAmount]),
DATEADD(DimDate[Datekey],-1,YEAR))
=IF(CALCULATE(SUM(FactSales[SalesAmount]),
DATEADD(DimDate[Datekey],-1,YEAR)),
SUM(FactSales[SalesAmount])/CALCULATE(SUM(FactSales[SalesAmount]),
DATEADD(DimDate[Datekey],-1,YEAR)),BLANK())
Result
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 269
Analysis
The second formula shows sales of the previous year. The third formula expresses the
current year as a ratio of the previous year. The fourth formula returns a blank instead
of a division-by-zero error. Please note that the third parameter here for DATEADD()
is YEAR and not YEAR(). The last formula is quite complex. You may want to break it
down into separate measures as follows:
=SUM(FactSales[SalesAmount])
=CALCULATE([Measure 1],DATEADD(DimDate[Datekey],-1,YEAR))
=IF([Measure 2],[Measure 1]/[Measure 2],BLANK())
You can further simplify the CALCULATE() function by using the measure itself as a
function. If you try this, the SetFilter argument must be in parentheses and there is
no comma after the measure. Here’s an example:
[Measure 1](DATEADD(DimDate[Datekey],-1,YEAR))
DATESBETWEEN()
Sales Amount from the Order Details table has been added to the Values drop-
zone before creating the measures. You will also need the Year, Quarter, Month, and
OrderDate columns from the Orders table. DATESBETWEEN() returns a range of dates.
The first parameter is the date column. The second and third parameters are the start
and end dates. The range of dates returned is inclusive of the start and end dates.
The examples are measures on the Order Details table.
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESBETWEEN(Orders[OrderDate],DATE(1997,12,1),DATE(1997,12,3)))
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESBETWEEN(Orders[OrderDate],DATE(1998,5,4),BLANK()))
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESBETWEEN(Orders[OrderDate],BLANK(),DATE(1996,7,5)))
270 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
BLANK(), as a start date, means from before the first date. BLANK(), as an end date,
means after the last date. The second example (Measure 2) shows the total from May 4,
1998, to the end of time. The only year with a value for this formula is 1998.
DATESINPERIOD()
DATESINPERIOD() also gives a range of dates. Instead of start and end dates, as in
DATESBETWEEN(), you use a start date and then a number of intervals. A positive
number takes you forward in time, and a negative number takes you back in time.
The example is a measure on the Order Details table.
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESINPERIOD(Orders[OrderDate],DATE(1996,7,4),5,DAY))
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 271
Result
Analysis
The start date here is July 4, 1996, and the number of intervals is 5. July 9 does not
qualify for the range—the potential dates are July 4, 5, 6, 7, and 8. July 6 and 7 are
not present, so the SUM() is for July 4, 5, and 8. Please note that the fourth interval
parameter for DATESINPERIOD() here is DAY and not DAY().
DATESMTD()
DATESMTD() returns all of the dates in the current month up to and including the
current date.
The example is a measure on the Order Details table.
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESMTD(Orders[OrderDate]))
272 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
This is effectively a running total that is restarted every month. The quarterly totals are
equal to those of the last month in the quarter. There is also a convenient alternative in
TOTALMTD():
=TOTALMTD(SUM('Order Details'[Sales Amount]),(Orders[OrderDate]))
TOTALMTD() is covered later in the chapter.
DATESQTD()
DATESQTD() returns all the dates in the current quarter up to and including the
current date.
The example is a measure on the Order Details table.
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 273
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESQTD(Orders[OrderDate]))
Result
Analysis
This time, the quarterly totals are equal to the sum of all the months in the quarter. If
you don’t want the lower-level totals to reset, you could override the date filter context:
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESQTD(Orders[OrderDate]),ALL(Orders))
You could also use TOTALQTD() to achieve the same result—the syntax is a little
more concise. TOTALQTD() is mentioned later in this chapter.