locked
YTD Average RRS feed

  • Question

  • Hi There,

    I have Day dimension with Year-quarter-month-Day, and with hierarchy set up YM (Year-Month), I have YTD amount calc like this

    SUM(YTD([Date].[YM]), [Measures].[AMOUNT])

    and also I want to calculate average YTD by month, so if it is Feb, the it will be SUM(YTD([Date].[YM]), [Measures].[AMOUNT])/2, April would be SUM(YTD([Date].[YM]), [Measures].[AMOUNT])/4 - count of month

    How do I get count of month here to work?

    Thanks


    Don

    Thursday, February 5, 2015 11:01 PM

Answers

  • Hi Dz0001,

    According to your description, you want to have your sum amount value divide by the count of month to get the average. Right?

    In this scenario, I assume the date dimension as under asc order, so we can get the last member of the date dimension, split the string of that member_key to get the month value. So please use the expression below:

    with member monthvalue as 
    cint(left(Right([Date].[YM].members.item(
    [Date].[YM].members.count-1).member_key,4),2))
    
     

    Please refer to my sample query with AdventureWorks:

    If you have any question, please feel free to ask.


    Simon Hou
    TechNet Community Support


    Tuesday, February 10, 2015 12:34 PM

All replies

  • Try something like Month(CDate( [Date].[YM].CURRENTMEMBER.MEMBER_VALUE))

    Friday, February 6, 2015 2:47 AM
  • Thanks Samiullah.

    I try your suggestion, it doesn't give me any error, but doesn't provide right result either, any other suggestion?


    Don

    Monday, February 9, 2015 3:13 AM
  • Hi Dz0001,

    According to your description, you want to have your sum amount value divide by the count of month to get the average. Right?

    In this scenario, I assume the date dimension as under asc order, so we can get the last member of the date dimension, split the string of that member_key to get the month value. So please use the expression below:

    with member monthvalue as 
    cint(left(Right([Date].[YM].members.item(
    [Date].[YM].members.count-1).member_key,4),2))
    
     

    Please refer to my sample query with AdventureWorks:

    If you have any question, please feel free to ask.


    Simon Hou
    TechNet Community Support


    Tuesday, February 10, 2015 12:34 PM