O'Reilly logo

Business Intelligence with MicroStrategy Cookbook by Davide Moraschi

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

Month-to-date and year-to-date transformations

A frequent requirement in reporting is to compare daily numbers with running monthly or yearly aggregates. These numbers are often referred to as MTD (month-to-date) and YTD (year-to-date) respectively. As always, there are more than one correct paths to get to the right values.

The technique explained in this recipe is one of them. It involves creating a reference table with two columns: the first contains the date we are referring to (let's call it the current date) and the second column holds every date that is needed to obtain the running total referred to the current date, for example all the days from the beginning of the month in case of the MTD.

Looking at the image, if I want to calculate a ...

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