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 month-to-date metric for July 3, 2005, I need to sum the three days: first, second, and third of July. The same applies to the YTD with (easy-to-guess) a lot more rows.
Think about it, in dimensional modeling words, as a helper table for a many-to-many...
