O'Reilly logo

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition by Sherry Li, Tomislav Piasevoli

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Calculating the year-to-date (YTD) value

In this recipe, we will look at how to calculate the YTD 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 SSMS and connect to your SQL Server Analysis Services (SSAS) 2016 instance. Click on the New Query button and check that the target database is Adventure Works DW 2016.

In order for this type of calculation to work, we need a dimension marked as Time in the Type property, in the Dimension structure ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required