Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
284 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
The entry for 2009 is blank because the entry for 200912 is blank. The latter is blank as
there is no 201001.
NEXTQUARTER()
NEXTQUARTER() retrieves the value from the next quarter if it’s used with a calculation
function.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Syntax
=CALCULATE(SUM('FactSales'[SalesAmount]),NEXTQUARTER(DimDate[Datekey]))
Result
Analysis
Once again, there is no entry for 2009.
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 285
NEXTYEAR()
NEXTYEAR() retrieves the value from the next year when used in conjunction with a
calculation function.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Syntax
=CALCULATE(SUM('FactSales'[SalesAmount]),NEXTYEAR(DimDate[Datekey]))
Result
Analysis
And again, there is no total for 2009. Also, note that 2006 (when there are no sales) is
shown as the measure does have a value—it’s the total for 2007.
OPENINGBALANCEMONTH()
OPENINGBALANCEMONTH() retrieves the closing balance from the previous month.
The balance is defined by the first, expression, parameter of the function. There is an
optional third parameter for defining a filter.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Syntax
=OPENINGBALANCEMONTH(SUM(FactSales[SalesAmount]),DimDate[Datekey])
286 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
The opening balance for 200705 is the same as the closing balance for 200704, which is
the sales value for April 30, 2007.
OPENINGBALANCEQUARTER()
OPENINGBALANCEQUARTER() retrieves the closing balance from the previous quarter.
Again, you can have a SetFilter argument as an optional third parameter.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Syntax
=OPENINGBALANCEQUARTER(SUM(FactSales[SalesAmount]),DimDate[Datekey])
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 287
Result
Analysis
The value of 20072 is the same as the closing balance of 20071, which is the value for
March 31, 2007.
OPENINGBALANCEYEAR()
OPENINGBALANCEYEAR() retrieves the closing balance from the previous year. You
can also have an optional third parameter for defining a filter. A fourth parameter
allows you to specify the year-end date—the default is December 31.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Syntax
=OPENINGBALANCEYEAR(SUM(FactSales[SalesAmount]),DimDate[Datekey])
288 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
The opening balance for 2008 is equal to the closing balance for 2007. That, in turn, is
the value for December 31, 2007.
PARALLELPERIOD()
PARALLELPERIOD() jumps ahead or back in time.
The examples are measures on FactSales. The formulas use ContosoRetailDW.
Syntax
=CALCULATE(SUM('FactSales'[SalesAmount]),
PARALLELPERIOD(DimDate[Datekey],-1,MONTH))
=CALCULATE(SUM('FactSales'[SalesAmount]),
PARALLELPERIOD(DimDate[Datekey],1,QUARTER))
=CALCULATE(SUM('FactSales'[SalesAmount]),
PARALLELPERIOD(DimDate[Datekey],1,YEAR))
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 289
Result
Analysis
If the second parameter is negative, PARALLELPERIOD() goes back in time. The
third parameter can be YEAR, QUARTER, or MONTH (please note these are enumerated
constants and not strings), but not DAY. PARALLELPERIOD() is similar to DATEADD().
However, the former always returns full periods and not partial periods. You can
demonstrate the difference by comparing the quarterly results for 2010 from the
following two examples:
=CALCULATE(SUM('FactSales'[SalesAmount]),
PARALLELPERIOD(DimDate[Datekey],-1,YEAR))
=CALCULATE(SUM('FactSales'[SalesAmount]),
DATEADD(DimDate[Datekey],-1,YEAR))
PREVIOUSDAY()
PREVIOUSDAY() gives the value from the previous day when combined with a
calculation function.
The example is a measure on the Order Details table.
290 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
PREVIOUSDAY(Orders[OrderDate]))
Result
Analysis
PREVIOUSDAY(), by itself, simply returns a date.
PREVIOUSMONTH()
PREVIOUSMONTH() gives the value from the previous month if used with a calculation
function.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 291
Syntax
=CALCULATE(SUM(FactSales[SalesAmount]),PREVIOUSMONTH(DimDate[Datekey]))
Result
Analysis
The figure for 200702 is the sales amount for 200701.
PREVIOUSQUARTER()
PREVIOUSQUARTER() gives the value from the previous quarter when you use it with
a calculation function.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Syntax
=CALCULATE(SUM(FactSales[SalesAmount]),
PREVIOUSQUARTER(DimDate[Datekey]))
292 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
The result for 20072 is the sales value for 20071.
PREVIOUSYEAR()
PREVIOUSYEAR() gives the value from the previous year if combined with a
calculation function. There is an optional second parameter for the year-end date—the
default is December 31.
The example is a measure on FactSales. The formula uses ContosoRetailDW.
Syntax
=CALCULATE(SUM(FactSales[SalesAmount]),PREVIOUSYEAR(DimDate[Datekey]))
Result
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 293
Analysis
The answer for 2009 is the same as the sales for 2008.
SAMEPERIODLASTYEAR()
SAMEPERIODLASTYEAR() jumps back one year. However, it does so at every date level.
The examples are measures on FactSales. The formulas use ContosoRetailDW.
Syntax
=CALCULATE(SUM(FactSales[SalesAmount]),
SAMEPERIODLASTYEAR(DimDate[Datekey]))
=CALCULATE(SUM(FactSales[SalesAmount])) -
CALCULATE(SUM(FactSales[SalesAmount]),
SAMEPERIODLASTYEAR(DimDate[Datekey]))
Result