Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
274 Practical PowerPivot & DAX Formulas for Excel 2010
DATESYTD()
DATESYTD() returns all the dates in the current year up to and including the current
date. There is also an optional second parameter for specifying the year-end date.
The examples are measures on the Order Details table.
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESYTD(Orders[OrderDate]))
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESYTD(Orders[OrderDate]),ALL(Orders))
Result
Analysis
Both examples give the same answer at the yearly level. The second example prevents
the lower-level running totals from resetting. You can achieve the same result with
TOTALYTD(), which is covered later in this chapter.
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 275
ENDOFMONTH()
ENDOFMONTH() returns the last date of the month.
The example is a measure on the Order Details table.
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
ENDOFMONTH((Orders[OrderDate])))
Result
Analysis
The value for the last day of a month becomes the month value. The value of the last
day of the last month of a quarter becomes the quarter value. The value of the last day
of the last month of the last quarter of a year becomes the year value.
ENDOFQUARTER()
ENDOFQUARTER() returns the last date of the quarter.
The example is a measure on the Order Details table.
276 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
ENDOFQUARTER(Orders[OrderDate]))
Result
Analysis
The result is similar to that of ENDOFMONTH() except that values for months that are
not at the end of a quarter are missing.
ENDOFYEAR()
ENDOFYEAR() returns the last date of the year.
The example is a measure on the Order Details table.
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 277
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
ENDOFYEAR(Orders[OrderDate]))
Result
Analysis
Again, this is similar, but now some quarterly values are missing, except for the last
quarter of each year.
FIRSTDATE()
FIRSTDATE() returns the first date in the current context.
The example is a measure on the Order Details table.
278 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
FIRSTDATE(Orders[OrderDate]))
Result
Analysis
The figure for May 1998 is the same as that for May 1, 1998. If the value for May 1
were blank, the value for May would also be blank. Similarly, the figure for 1998 will be
the same as that for the first quarter in 1998, and so on.
FIRSTNONBLANK()
FIRSTNONBLANK() is similar to FIRSTDATE() except it finds the first value that is
not blank. FIRSTNONBLANK() can be used on other columns, not just dates.
The example is a measure on the Order Details table.
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 279
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
FIRSTNONBLANK(Orders[OrderDate],SUM('Order Details'[Sales Amount])))
Result
Analysis
The figure for May 1998 is the same as that for May 1, 1998. If the value for May 1
were blank, the value for May would be the first non-blank value found in May.
LASTDATE()
LASTDATE() returns the last date in the current context.
The examples are measures on the Order Details table.
280 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
LASTDATE(Orders[OrderDate]))
=CALCULATE(SUM('Order Details'[Sales Amount]),
LASTDATE(Orders[OrderDate])) - CALCULATE(SUM('Order Details'[Sales
Amount]),
FIRSTDATE(Orders[OrderDate]))
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESBETWEEN(Orders[OrderDate],BLANK(),LASTDATE(Orders[OrderDate])))
=CALCULATE(SUM('Order Details'[Sales Amount]),
DATESBETWEEN(Orders[OrderDate],BLANK(),LASTDATE(Orders[OrderDate])),
ALL(Products))
Result
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 281
Analysis
LASTDATE(), FIRSTDATE(), and BLANK() are often used to establish date ranges.
There are a few examples here. Try them one at a time to understand what the DAX
is doing. The second example shows the increase (or decrease) from the first to the last
day within the current context. In 1997, sales on the last day of the year for Beverages
were 124 down on the first day. The last example ignores the Category slicer.
LASTNONBLANK()
LASTNONBLANK() is similar to LASTDATE(). However, if the value for the last date is
blank, it will search backward in time until it finds the first non-blank value.
The example is a measure on the Order Details table.
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
LASTNONBLANK(Orders[OrderDate],SUM('Order Details'[Sales Amount])))
Result
282 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
The last non-blank value for 1998 is 749, when sliced by Beverages. The last non-blank
values for Q2 and May are also 749. However, the last non-blank value for Q1 1998 is 276.
NEXTDAY()
NEXTDAY() retrieves the value from the next day when used with a calculation
function. NEXTDAY(), itself, returns the date of the next day.
The example is a measure on the Order Details table.
Syntax
=CALCULATE(SUM('Order Details'[Sales Amount]),
NEXTDAY(Orders[OrderDate]))
Result
Chapter 9: Date & Time Functions 2/2: Time Intelligence Functions 283
Analysis
There is no value for May 1, 1998, as May 2 does not exist or is a blank. NEXTDAY()
does not cross higher-level boundaries.
NEXTMONTH()
NEXTMONTH() retrieves the value from the next month when used in conjunction with
a calculation function.
The example is a measure on the FactSales table. The formula uses ContosoRetailDW.
Before creating the measure, add SalesAmount from FactSales to the Values drop-zone.
Also, place CalendarYear, CalendarQuarter, CalendarMonth, and Datekey on the Row
Labels of the pivot table. When you create the measure, place it in the FactSales table.
Syntax
=CALCULATE(SUM('FactSales'[SalesAmount]),NEXTMONTH(DimDate[Datekey]))
Result