troubleshooting Question

Determining previous week members for the AVG function in MDX

Avatar of Ruaghain
RuaghainFlag for Ireland asked on
DatabasesMicrosoft SQL Server
3 Comments1 Solution1132 ViewsLast Modified:
Hiya all,

I'm fairly new to the MDX world so appologies if the question seems a wee bit daft. I'm looking to determing the average of a measure over several weeks, what the lads are looking for is basically the average of all weeks upto and including the current week e.g. Average for week 2 for any given year would be average measure over week1 : week2, average for week 7 would be average measure over week1 : week7. Is there any way represent this in a calculated measure? I was thinking of using the lag function, but am falling short of determining how far I have to go back (See code snippet).

Any help would be greatly appreciated.

R.
With
Member [Measures].[AverageWeeklyCases] as
 Avg([Time].[YearWeekDate].CURRENTMEMBER.Lag(1) : [Time].[YearWeekDate].CurrentMember, [Measures].[Count])
select
{[Measures].[AverageWeeklyCases]} on columns,
{[Time].[YearWeekDate].Week} on rows
from [Cube]
where [Time].[Year].&[2008-01-01T00:00:00]
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros