none
How to get average of YTD

    Question

  • Hi,

     

    I have a requirement that calculates average of ytd.

     

    So if I filter months from my time dimension. Say April 2008, the Average YTD should calculate the YTD / number of months starting from Jan of the same Year.  So for July 2008, it should calculate ytd/7.

     

     

     

    Regards,

    Larry

     

     

     

     

     

     

     

     

     

     

     

     

    Tuesday, July 08, 2008 3:49 PM

Answers

  •  

    Hi Larry,

     

    Code Snippet

    [Measures].[AVG YTD] AS

    ' Sum(YTD(), [Measures].[Sales]) / Count(YTD()) '

     

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Tuesday, July 08, 2008 4:09 PM
    Answerer
  • Hi,

     

    I have a simple example from the Adventure Works cube that might help:

     


    Code Snippet

    WITH MEMBER Measures.MyYTD AS
    SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])

    MEMBER Measures.MyMonthCount AS
    SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year])))

    MEMBER Measures.MyYTDAVG AS
    Measures.MyYTD /  Measures.MyMonthCount

    Select  {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0,
     [Date].[Calendar].[Month] On 1
    From [Adventure Works]
    Where ([Date].[Month of Year].&[7])

     

     

    HTH

    Thomas Ivarsson

    Tuesday, July 08, 2008 6:10 PM
    Moderator

All replies

  •  

    Hi Larry,

     

    Code Snippet

    [Measures].[AVG YTD] AS

    ' Sum(YTD(), [Measures].[Sales]) / Count(YTD()) '

     

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Tuesday, July 08, 2008 4:09 PM
    Answerer
  • Hi,

     

    I have a simple example from the Adventure Works cube that might help:

     


    Code Snippet

    WITH MEMBER Measures.MyYTD AS
    SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])

    MEMBER Measures.MyMonthCount AS
    SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year])))

    MEMBER Measures.MyYTDAVG AS
    Measures.MyYTD /  Measures.MyMonthCount

    Select  {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0,
     [Date].[Calendar].[Month] On 1
    From [Adventure Works]
    Where ([Date].[Month of Year].&[7])

     

     

    HTH

    Thomas Ivarsson

    Tuesday, July 08, 2008 6:10 PM
    Moderator