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 ...
Get Business Intelligence with MicroStrategy Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
