Cumulative or Running totals in MDX

Hi all

I am trying to create the cumulativeactivitycount in this example in my cube db:

I have managed it but have done so using a count of months in the year and a case statement so:

when [Date From Source Data].[Fiscal Month] = [Date From Source Data].[Fiscal Month].[Fiscal Month Name].&[1]
then ([Measures].[Activity Count],[Date From Source Data].[Fiscal Month].[Fiscal Month Name].&[1])
when [Date From Source Data].[Fiscal Month] = [Date From Source Data].[Fiscal Month].[Fiscal Month Name].&[2]
then ([Measures].[Activity Count],[Date From Source Data].[Fiscal Month].[Fiscal Month Name].&[1])+([Measures].[Activity Count],[Date From Source Data].[Fiscal Month].[Fiscal Month Name].&[2]) and so on and so forth right up to 12...

The problem with this is that it is really slow and is causing performance issues with any report suite that tries to use it.

I've also tried:

SUM({NULL:[Date From Source Data].[Fiscal YQM].currentmember},[Measures].[Activity Count])

Which works as it should do but doesn't restart in April at the beginning of each Fiscal Year.

The PeriodsToDate and YTD, MTD, QTD functions in MDX seem to give you just one value across all months which is the total of the accumulated value for the year which is not what I want.

Any help here would be gratefully received. The cumulative member I created in the first example worked OK for one organisation but it now has to work for 23 (so in other words we have over 23 times the amount of records in the fact table) so I need something slicker.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Your hierarchy does not appear to be optimally set up.

You should have it: Fiscal Year - Fiscal Month.

This may explain why YTD is not behaving the way it should.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trawleyAuthor Commented:
I had tried this before but no difference. However your comment about YTD spurred me to try the following using an hierarchy that I had already set up called Fiscal Year Month which had the Year and Month members only. (I had tried this in the original calculation but it didn't like it.)

So this now works fine:

aggregate( ytd([Date From Source Data].[Fiscal Year Month]), [Measures].[Activity Count])

So Phillip whilst you didn't answer my question directly, you got my mind into the correct area so for that I will give you half the points.


trawleyAuthor Commented:
Reasons in comment above.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.