Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 145

Year-to-date calculations
Year-to-date calculations calculate the aggregation of values from the 1 st of January
up to the current date. In order to create a year-to-date calculation, we need to use
the YTD function, which takes a single date member as a parameter and returns the
set of members on the same hierarchy as that member starting from the first day of
the year up to and including the selected member. So, for example, if we passed the
member 23 rd July into the YTD function, we'd get a set containing all of the dates from
1 st January to 23 rd July; it also works at higher levels, so if we passed in the member
Quarter 3, we'd get a set containing Quarter 1, Quarter 2, and Quarter 3.
In order for the YTD function to work properly, you need to have a dimension whose
Type property is set to Time , an attribute hierarchy in that dimension whose Type
property is set to Years , and a user hierarchy including that attribute hierarchy (see
the section Configuring a Time dimension in Chapter 2 , Building Basic Dimensions and
Cubes , for more details on the Type property). This is because the way the function
works is to take the member you pass into it, find its ancestor at the Year level, find
the first member of that year on the same level as the original member we passed in,
and then return the set of all members between the first member in the year and the
member we passed in.
Passing a static member reference into the YTD function is not going to be very
useful - what we want is to be able to make it dynamic and return the correct
year-to-date set for all of the dates we use in our queries. To do this, we can use the
CurrentMember function on our date hierarchy, and use it to pass a reference to the
current date member from the context of our query into our calculated member.
Finally, the YTD function only returns a set object, and what we need to do is to
return the sum of that set for a given measure. As a result, our first attempt at writing
the calculated member, building on what we saw in the previous section, might be
something like this:
CREATE MEMBER CURRENTCUBE.Measures.[Sales Amount YTD] AS
SUM (
YTD ([Date Order].[Calendar].CurrentMember),
Measures.[Sales Amount]);
This computes the Sales Amount YTD member as the sum of the sales amount from
the first date in the year up to and including the currently selected member in the
date order calendar hierarchy.