Microsoft Office Tutorials and References

In Depth Information

The last parameter corresponds to June 30, which in our OrderDate table corresponds to the

end of the fiscal year. You can find several Time Intelligence functions that have a last, optional

YE_Date parameter for this purpose: STARTOFYEAR, ENDOFYEAR, PREVIOUSYEAR, NEXTYEAR,

DATESYTD, TOTALYTD, OPENINGBALANCEYEAR, and CLOSINGBALANCEYEAR.

Periods from the Prior Year

People commonly need to get a value from a period of the prior year (PY). This can be

useful for making comparisons of trends, during a period last year to the same period this year,

as you can see in the CH07-08-Aggregation.xlsx workbook included on the companion DVD.

This is the DAX expression you need to calculate that value:

PyLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),

SAMEPERIODLASTYEAR( OrderDate[Date] ) )

The CALCULATE function changes the filter by using the SAMEPERIODLASTYEAR function,

which returns a set of dates shifted one year back in time. The SAMEPERIODLASTYEAR

function is a specialized version of the more generic DATEADD function, which can

be used by specifying the number and type of periods to shift. For example, the same

PyLineTotal measure can be defined by this equivalent expression:

PyLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),

DATEADD( OrderDate[Date], -1, YEAR ) )

Sometimes you must look at the total amount of a measure for the previous year, usually

to compare it with the year-to-date total. To do that, you can use the PARALLELPERIOD

function, which is similar to DATEADD but returns the full period specified in the third

parameter instead of the partial period returned by DATEADD. The PyTotLineTotal measure

that calculates the total sum of LineTotal for the previous year can be defined this way:

PyTotLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),

PARALLELPERIOD( OrderDate[Date], -1, YEAR ) )

In Figure 7-40, you can see the result of the PyLineTotal and PyTotLineTotal measures. The quarters

data in 2002 for the Sum Of LineTotal column has been copied into the respective quarters of

year 2003 in the PyLineTotal column. The PyTotLineTotal simply reports for every period the

total amount of the LineTotal column for the year before.