YTD MDX Question

  • I have the following MDX calculation in my SSAS cube that returns Prior YTD Sales based on the current date.

    Sum(YTD(ParallelPeriod([Calendar].[Year-Quarter-Month-Week-Day].[Year],1,[Calendar].[Year-Quarter-Month-Week-Day].CurrentMember)),[Measures].[Net Sales])

    This will return the YTD sales for prior year, however I need a calculation to return the total sales for the entire prior year.

    (based on today's date)

    Example: Current YTD sales : $5,000,000

    Prior YTD sales : $5,100,000

    Prior Year Sales: $8,000,000 (this is the calc I can't figure out)

    Any help would be greatly appreciated...

  • randy.jaye (9/16/2010)


    I have the following MDX calculation in my SSAS cube that returns Prior YTD Sales based on the current date.

    Sum(YTD(ParallelPeriod([Calendar].[Year-Quarter-Month-Week-Day].[Year],1,[Calendar].[Year-Quarter-Month-Week-Day].CurrentMember)),[Measures].[Net Sales])

    This will return the YTD sales for prior year, however I need a calculation to return the total sales for the entire prior year.

    (based on today's date)

    Example: Current YTD sales : $5,000,000

    Prior YTD sales : $5,100,000

    Prior Year Sales: $8,000,000 (this is the calc I can't figure out)

    Any help would be greatly appreciated...

    any specific reason for not implementing...

    SELECT [MEASURES].[SALES] ON 0

    WHERE [DATE DIMENSION SLICE]=YEAR

    Raunak J

  • I am trying to build a calculated member in a SSAS cube. I am relatively new to this process so I may not be aware of all the techniques.

    The vast majority of our reporting and analysis is ver specific to a "day". And from the "day" being reported against or analyzed, we need an easy way to provide a calculated measure that provide YTD, Prior YTD,etc. based on day the user selects. I am just not clear on how to determine the Total Prior Year Sales based on a supplied date. For example, if user (or report) uses a day of 9/20/2010 then I need to total sales for 2009.

    I hope this makes sense. I guess you could call me a newbie.

  • randy.jaye (9/17/2010)


    I am trying to build a calculated member in a SSAS cube. I am relatively new to this process so I may not be aware of all the techniques.

    The vast majority of our reporting and analysis is ver specific to a "day". And from the "day" being reported against or analyzed, we need an easy way to provide a calculated measure that provide YTD, Prior YTD,etc. based on day the user selects. I am just not clear on how to determine the Total Prior Year Sales based on a supplied date. For example, if user (or report) uses a day of 9/20/2010 then I need to total sales for 2009.

    I hope this makes sense. I guess you could call me a newbie.

    Jaye,

    The CURRENTMEMBER attribute will not help you to get the Total yearly sales

    I suppose you will have to fire an all together new MDX for total sales, as mentioned above

    Raunak J

  • WITH MEMBER [MEASURE_NAME] AS

    AGGREGATE

    (

    PERIODSTODATE

    (

    [Date].[Calendar].[Calendar Year],

    [Date].[Calendar].CurrentMember

    ),

    ([Measures].[Sales Amount])

    )

    Note: This Measure will give you a cumulative value of total sales till current value

    Raunak J

  • I am a newbie also and have been getting along fine calculating YTD information but now they have asked me to come up with a Prior Year End number and am having trouble figuring out the code. I found your post and it appears as though you were trying to figure out the same issue. I am wondering if you ever got a solution to your problem and if so if you can forward it on to me.

    What I am trying to find is this. The total sales for 2013 where today is 08/31/2014. I can find YTD for 08/31/2014 and YTD for 08/31/2013, but finding YTD for 12/31/2013 has me stymied. Thanks.

  • Have a look at "Time Intelligence" in Book online. It is a wizard that you can use (in BIDS/SSDT) to generate the MDX code for various time based calculations.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply