# Calculate Average in MDX • ### Question

• Hi All,

I want to calculate Average of Net Revenue for Last 4 Mondays . Example , if we run the report on Wednesday then we should get Average of last 4 Wednesday's.

I came up with the following MDX expression :

Avg

(

{[Date].[Fiscal Date].

currentmember,[Date].[Fiscal Date].currentmember.lag(7),[Date].[Fiscal Date].currentmember.lag(14),[Date].[Fiscal Date].currentmember.lag(21)}

,

[Measures].[Net Revenue])

Any Suggestions for a better solution for this ?

Wednesday, June 30, 2010 10:56 PM

• Hi Ngulati,

Here is some mdx to get the avg of the last 4 Mondays.  You might need to adapt it, if you want the "preceding 4 mondays".  Also, I assume that you have a hierarchy called [Day of Week], which contains the week day.

Avg

(tail(nonempty(

{[Date].[Fiscal Date].[Fiscal Day]

*[Date].[Day Of Week].[Monday],[Measures].[Net Revenue]),4),

,[Measures].[Net Revenue])

Richard
Thursday, July 1, 2010 1:44 AM
• No problem,

Just need to put the currentmember as the end of the set.   Try this

Avg

(tail(nonempty(null:[Date].[Fiscal Date].currentmember

*[Date].[Day Of Week].[Monday],[Measures].[Net Revenue]),4)

,[Measures].[Net Revenue])

Richard
Thursday, July 1, 2010 9:05 PM

### All replies

• Hi Ngulati,

Here is some mdx to get the avg of the last 4 Mondays.  You might need to adapt it, if you want the "preceding 4 mondays".  Also, I assume that you have a hierarchy called [Day of Week], which contains the week day.

Avg

(tail(nonempty(

{[Date].[Fiscal Date].[Fiscal Day]

*[Date].[Day Of Week].[Monday],[Measures].[Net Revenue]),4),

,[Measures].[Net Revenue])

Richard
Thursday, July 1, 2010 1:44 AM
• seems there is a syntax issue with that MDX Richard. Also, where does the "4" come into play?
FJK
Thursday, July 1, 2010 5:33 PM
• Ok, after some thought and research I figured out what you are doing with that calc and where the syntax error was.

FJK
Thursday, July 1, 2010 6:19 PM
• Thanks Richard for the Reply !

It works fine for the Current Date when you run the query.But I am looking for a solutions where I could run the report at any point in time to look for last 4 Mondays. Example , if the user select this calculated measure [Measures].[Avg 4 Week] on any day in the past , the measure should return 4 weeks average from that point of time.

Thanks,

Ngulati

Thursday, July 1, 2010 6:30 PM
• No problem,

Just need to put the currentmember as the end of the set.   Try this

Avg

(tail(nonempty(null:[Date].[Fiscal Date].currentmember

*[Date].[Day Of Week].[Monday],[Measures].[Net Revenue]),4)

,[Measures].[Net Revenue])

Richard
Thursday, July 1, 2010 9:05 PM