Hi there!

Could someone please help me with this.
I urgently need a formula on getting the current year YTD.
For instance, given that I have data for 2005 and 2006. If YTD for Dec
2005 is 100 and Jan 2006 is 50, the count for Dec 2005 should not be
added to the YTD for 2006. Meaning, it will reset and the 2006 YTD
should be 50 and not 150.

I hope someone could help me. Thanks!

Depending on your time dimension hierarchy. If you have a year level in your
time hiearchy, you could use YTD() function.

"kaye" wrote:

My time dimension is not really a Time Dimension. It functions just
like a normal dimension 'coz I already have a table where all possible
time hierarchies are recorded. (Year, Quarter, Semi-Annual, Month,
etc.) Can I possibly generate a YTD report for this year only using
that kind of time dimension?

yongli wrote:

Can you describe the structure of your time dimension, even if it isn't
tagged as a "Time" dimension - does it have levels like Year, Month,
Day? If so, you could probably use PeriodsToDate(), like:

PeriodsToDate([Time].[Year], [Time].CurrentMember)


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Sir,

I really appreciate your reply.

My time dimension (SAT Period) only have levels Year and Month.
I tried using the PeriodsToDate but it displays an error:

Unable to update the calculated member.
Formula error - cannot convert set to Expression - in the PeriodsToDate
function.

Here's the current formula I'm using for the YTD, but it outputs YTD
count since the first recorded year, and not 2006 only:

SUM({[SAT Period].CurrentMember.Level.Members}.Item(1); [SAT
Period].CurrentMember, [Measures].[TMO])

How can I reformulate the formula I'm using right now to come up with a
YTD count for every year only?

Thanks alot Sir!


Deepak Puri wrote:

You can try this:

SUM(PeriodsToDate([SAT Period].[Year],
[SAT Period].CurrentMember), [Measures].[TMO])


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Sir Deepak,

Thank you so much for all the help and assistance! It worked! yey!
hehehe..
Sorry for the replying so late. I haven't checked the groups for quite
some time.

Thank you so much again! God bless.. *hugs*

- kaye Ü


Deepak Puri wrote: