# Sum Accross Years, Quarters, and Month [Time].[Year-Quarter-Month-Date]

• ### Question

• How do I get to sum across Year, Quarter, and Month Time multi-select tree filter?

SELECT
NON EMPTY [Time].[Year -  Quarter -  Month -  Date].AllMembers
ON ROWS,
NON EMPTY { [Measures].[Result], [Measures].[Target], [Measures].[Previous Result] }
ON COLUMNS
FROM [OCParks]
WHERE ( [MEASURE].[MEASURE NAME].&[Park Visitors] )

My colleague used the following tuple formula and they did not work.

aggregate({EXISTS([Time].[Month].[Month].Members,[FilterMonth]).Item(0):[Time].[Month].CurrentMember},[Measures].[MEASURE VALUE])

sum({EXISTS([Time].[Month].[Month].Members,[FilterMonth]).Item(0):[Time].[Month].CurrentMember},[Measures].[MEASURE VALUE])

Where do you insert you mdx query? In the KPI tuple formula or because its a dynamic filter on a scorecard to filter connection formula? My scorecard is connected to row and the source value is member unique name.

..see next message.

Thursday, August 30, 2012 6:09 PM

### All replies

• This scorecard should of start with Previous Year Result Cumulative - July 1,804,418, Target Cumulative - July 7,106,385, and Result Cumulative - July 7,7207,586 then the next month Previous Year Result Cumulative August is July + August = 3,444,031, Target Cumulative - August + July = 3,616,233, Result Cumulative - August + July = 3,642,505 and this has to dynamically change based having the option to start by any particular month, quarter, or year.

Any ideas?

Thursday, August 30, 2012 6:10 PM
• It would be best if you move all your calculations to your cube's calculated measures. What you looking for is a running total across all time periods. Here you will find couple of ways to do that:

Mdx Running Total

Running Total with Recursive MDX

You might also need SCOPE since level of your time dimension change from day to week to month etc.

http://dailyitsolutions.blogspot.com/

Friday, August 31, 2012 1:59 PM