[b]YTD and Previous YTD Comparisons - Assistance Required [/b]

[Nassy]

Hi everyone,

The Background

I have been asked to add some 'to date' time analysis to a cube developed by one of our clients. This cube has two measures:

Invoiced Amount
Units Sold

The client wants to make Year to Date and Month to Date comparisons for both these numeric values.

What I have achieved so far

I have written 2 MDX calculations, one for the Invoiced Amount and one for Units Sold as follows:


i.
Sum ( PeriodsToDate ([Calendar Date].Year), [Measures].InvoicedAmount)

ii.
Sum ( PeriodsToDate ([Calendar Date].Year), [Measures].UnitsSold)


These two calculations allow me to display YTD totals for the two measures.

The Problem

The problem however arises when I try and write formula for these measures that gets the Previous Year to Date.

The way I have tried to tackle this so far - to no avail - is to use the ParallelPeriods() function but no matter *where* I place the ParallelPeriod part nothing seems to work.

I am new to MDX and am wondering if I am approaching this all wrong. For example:

Am I thinking along the right lines or is it best to make YTD and PreviousYTD part of the Time Dimension ?

The only other thing to note is that this cube has two time dimensions - one for Calendar Date and one for Fiscal date so my YTD functions (so far) work only for Calendar Date. Should I also write 'sister' functions that work with the Fiscal Date dimension.

Apologies for all this, but I am new to MDX and can find very little online that can help me resolve these issues.
I have got to the stage where I am now pulling my hair out so any help at all would be much appreciated



Nassy

 

[Adamba]

Nassy,

Any luck With your rolling Last Year -YTD?

Regards,



Adam

 

[Nassy]

No - requirement to do this was dropped - I researched it for a while and tried different things but then gave up ;-<

Unfortunately getting help with OLAP stuff isn't as easy as getting help with standard SQL stuff... (for obvious reasons I guess).

Cheers

Nassy