Calculating the YTD (Year-To-Date) value

In this recipe we will look at how to calculate the Year-To-Date value of a measure, that is, the accumulated value of all dates in a year up to the current member on the date dimension. An MDX function YTD() can be used to calculate the Year-To-Date value, but not without its constraints.

In this recipe, we will discuss the constraints when using the YTD() function and also the alternative solutions.

Getting ready

Start SQL Server Management Studio and connect to your SSAS 2012 instance. Click on the New Query button and check that the target database is Adventure Works DW 2012.

In order for this type of calculation to work, we need a dimension marked as Time in the Type property, in the Dimension Structure tab of SSDT. That should not be a problem because almost every database contains at least one such dimension and Adventure Works is no exception here. In this example, we're going to use the Date dimension. We can verify in SSDT that the Date dimension...