mdx query to calculate average between date range

Question

I am hoping to get some help to calculate the average between date ranges. Start date would be the time dimension and end date = start date - 13. Or is there a way we can use iff statement to calculate the average with date range?

Answer 1

This is AVG in MDX: https://learn.microsoft.com/en-us/sql/mdx/avg-mdx

The functions signature is this:

Avg( Set_Expression [ , Numeric_Expression ] )  

So the Set_Expression will be the dates and the optional Numeric_Expression could be say a measure.

If you have a specific date such as [Ship Date].[Date].[Date].[10 Feb 2018] then you can go backwards using the lag function - then you can create a range using a colon operator.

Therefore you might end up with an expression like this:

AVG(
   [Ship Date].[Date].[Date].[10 Feb 2018].lag(13)
 : [Ship Date].[Date].[Date].[10 Feb 2018]
 ,[Measures].[Revenue]
)

So the above is not so dynamic but if the hierarchy [Ship Date].[Date].[Date] is ON ROWS then you can use the CURRENTMEMBER function:

AVG(
   [Ship Date].[Date].CURRENTMEMBER.lag(13)
 : [Ship Date].[Date].CURRENTMEMBER
 ,[Measures].[Revenue]
)