|MDX-How can I get Last (Previous) Year to Date (YTD) values?|
|Written by Vidas Matelis|
|Tuesday, 01 May 2007 19:37|
Q: How can I get Last (Previous) Year to Date (YTD) values?
A: First lets clarify question. Lets say you selected date March 22, 2004. Year To Date means you are interested in the interval from January 1, 2004 up to March 22, 2004. Last (Previous) Year to date means you are interested in the intervale January 1, 2003 up to March 22, 2003.
With certain exceptions, you should be able to use function ParallelPeriod to get a date in the previous year matching your selected date. Assumption here is that you have similar members in each years hierarchy. If, for example, you have just business days in day hierarchy, matching might be different.
Example: Lets say you selected March 22, 2004. If we use ParallelPeriod function, we can get back member for date March 22, 2003. Query Example:
As you know how to get previous years matching day, you can calculate YTD for previous year:
Now lets run the same query, but substitute YTD and Parallel periods with hardcoded dates. This way we will make sure that calculation is right.
Results match previous query:
Note: You can substitute YTD function with PeriodsToDate function.
These tests were done on Adventure Works database.
- How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project
- MDX-How can I get Last (Previous) Year to Date (YTD) values?
- What TCP port SQL Server Analysis Services 2005 uses
- MDX-How do you calculate monthly average of one year, optionally including empty months?
- MDX-How do I calculate sales for 12 Month to date?