Calculating average (x) over last 3 months

Question

Calculating average (x) over last 3 months

Sam Peascod

SSCrazy

Points: 2497
More actions
May 17, 2007 at 10:43 pm

#124066

I need to create a calculated member which takes the average of a field in my fact table over the last 3 months. In my fact table, I have once-daily records showing the cost of an item, and it's this cost field that I need to average.
What's the most efficient way to do this (obviously using MDX)?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Have you looked through Bill Pearson's articles (like this one) to see if his walk-through of a Rolling Avergae might be applicable? If not directly applicable it's possible it will give you ideas of how to implement what you do need. He has a whole series on MDX, another on RS and i'm sure some more combining the two - definitely worth a look for some ideas

Steve.

Sam Peascod SSCrazy Points: 2497 More actions · Answer 2

Thanks Steve, I should've checked there first. Looks like I'll be able to work it out from there.

Sam Peascod SSCrazy Points: 2497 More actions · Answer 3

I can virtually use Bill's formula, which is great. The formula I have is:

Avg (LastPeriods (3, [Time].[FiscalMonth].CurrentMember), [Measures].[Cc Matl Cost])

Note that my Time dimension has four hierarchies: fiscal week/month and calendar month/week. Is there any way I can exclude the hierarchy from my formula, or do I need to define four rolling average functions? If I just use [Time], I get a "member cannot be found" error.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Hey Sam,

Are you using 2005 or 2000? If 2005 then I think you might have to write your formula using either case or nexted IIF's to pre-determine the hierarchy. There is a detailed paper (of sorts) by Richard Tkachuk (close to Mosha on the dev team) about Attribute Relationships which then leads to a discussion of CurrentMember and it's affect on other attributes in a query/calculation.

Steve.