locked
Help needed for calculating YTD values for fiscal years RRS feed

  • Question

  • Hi,

         I have some problem calculating YTD values for fiscal years.Our fiscal year starts on sep 1st and ends on 30th aug.I need to make my query parameterized.I want to take the date passed in and want to travel till that year start date.Do I need to build any hierarchy for fiscal calendar?How to set my MDX expression calculates YTD for the currentmember i.e the current date?How can I set current date as my currentmember?

    I have one more thing to do that I need to get the sum for last 2 yrs and last 3 yrs from the current date.Please help me...Thank you,

    Monday, August 16, 2010 1:55 PM

Answers

  • No, Currentmember references the relative member in the query.  i.e. if you were getting back sales by year and it looked like this:

    FY 2009  500

    FY 2010  700

    As the MDX is calculated, it references the current member as it makes it's calculation.  So if you were referencing the currentmember in the date dimension, it would reference fiscal year 2009 the first time that is calculated and the fiscal year 2010 the second time it is calculated. 

    Another example that will make more sense for what you are working on, consider sales and YTD:

    Month   Sales   YTD

    Jan      100      100

    Feb      20       120

    Mar      110     230...

    Hope that makes sense. 

    Mark

    • Marked as answer by Raymond-Lee Tuesday, August 24, 2010 12:03 PM
    Monday, August 16, 2010 8:13 PM

All replies

  • Hi,

    You do need fiscal time periods defined in your date dimension.  Presuming you have a date table in your relational database, you would include Fiscal Year, Quarter and month, for example from this table, in creating your date dimension.  You then need to create a hierarchy, something like Year - Qtr - Mth - Date, which will allow you to navigate the dimension. 

    I would look at the adventures works sample on how to configure the date dimension, because they have a pretty good basic example there, which can show you how to configure composite keys, setting the dimension type and that sort of thing. 

    Once your date dimension is complete and reprocessed the cube, you are then ready to query.  Because this is fiscal time, you cannot use the built in functions for time, i.e. YTD(), MTD().  You can, however, use PeriodsToDate() which can get you year to date, the last 2 or 3 years or whatever. 

    These calculations can reference the current member and be added to the cube in the calculations tab.  This database journal artical has information on working with the currentmember and time:

    http://www.databasejournal.com/features/mssql/article.php/2232111/MDX-Essentials---MDX-Time-Series-Functions-Part-I-PeriodsToDate-and-Kindred-Functions.htm

    Good luck,

    Mark

    Monday, August 16, 2010 7:18 PM
  • Thanks for your reply.My time dimension has day as the lowest granularity.Is currentmember going to be my current day as my table goin to get daily stamped?
    Monday, August 16, 2010 7:58 PM
  • No, Currentmember references the relative member in the query.  i.e. if you were getting back sales by year and it looked like this:

    FY 2009  500

    FY 2010  700

    As the MDX is calculated, it references the current member as it makes it's calculation.  So if you were referencing the currentmember in the date dimension, it would reference fiscal year 2009 the first time that is calculated and the fiscal year 2010 the second time it is calculated. 

    Another example that will make more sense for what you are working on, consider sales and YTD:

    Month   Sales   YTD

    Jan      100      100

    Feb      20       120

    Mar      110     230...

    Hope that makes sense. 

    Mark

    • Marked as answer by Raymond-Lee Tuesday, August 24, 2010 12:03 PM
    Monday, August 16, 2010 8:13 PM