Hi all,

WITH MEMBER [Measures].[RunningSum] AS 'Sum(PeriodsToDate([Time].[Year]),
[Measures].[Sales])'
MEMBER [Measures].[SalesLastYear] AS '(ParallelPeriod([Time].[Year]),
[Measures].[Sales])'
MEMBER [Measures].[RunningSumLastYear] AS 'Sum(PeriodsToDate([Time].[Year]),
[Measures].[SalesLastYear])'

SELECT {[Measures].[RunningSum], [Measures].[RunningSumLastYear]} ON
COLUMNS,
{ [Time].[Year].Members } ON ROWS
FROM NetGear

the query above returns:

2003 863,913
2004 688,548 863,913
2005 475,078 688,548
2006 7,192 475,078

The year 2006 contains only data for january.

Is there a way to define a running sum that only sums the sales from
previous year, when there are corresponding sales in the current year?

The figure above should look like:

2003 863,913
2004 688,548 863,913
2005 475,078 688,548
2006 7,192 55,078 (the sum from january 2005)

Thanks in advance,

Max

Hi Max,

The query is accessing [Time] at the [Year] level, so
PeriodsToDate([Time].[Year]) is returning just that year.

Assuming that data is loaded at the [Month] level, you could use
NonEmptyCrossJoin() or Filter() to identify the last month with data and
sum over months, like:

'Sum(PeriodsToDate([Time].[Year],
Tail(NonEmptyCrossJoin(Descendants([Time].CurrentMember,
[Time].[Month]), {[Measures].[Sales]}, 1)).Item(0).Item(0)),
[Measures].[SalesLastYear])'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks Deepak,

that's both, perfekt and elegant!

I also played around trying to create a measure that sums the sales in every
year in the past just to the month of the current month in the current year.
Well changing .CurrentMember to .AllMembers does almost what I want, but i
stops working, when I drill down into the quarter or month level.

Do you have a solution for something like the following, that also handles
drill down:

2003 863,913
2004 688,548 863,913 45,456
2005 475,078 688,548 54,456
2006 7,192 55,078 55,078

(the last column shows only the sum to the current month/current year
period).

thanks,

Max



"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> schrieb im Newsbeitrag
news:OExuoLBNGHA.3832 (AT) tk2msftngp13 (DOT) phx.gbl...

Hi Max,

Something along these lines works with the Adventure Works:

'Sum(PeriodsToDate([Time].[Year],
Cousin(Tail(NonEmptyCrossJoin(Descendants([Time].DefaultMember,
[Time].[Month]), {[Measures].[Sales]}, 1)).Item(0).Item(0),
Ancestor([Time].CurrentMember, [Time].[Year]))),
[Measures].[SalesLastYear])'
A couple of points to clarify:

- Even if the current member is earlier than the current month,
summation will still occur to the current month

- Cousin() works if all years have the same set of months


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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