# YTD Average

• ### 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

• 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