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.
Search JabSto ::




Custom Search