1

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?

SKenneth
  • 19
  • 11

1 Answers1

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]
)
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • Hi - The above calculated measure worked for me. I have a dimension with values as Type a, Type b. How can I add only type b value to be calculated in the calculation below : AVG( [Ship Date].[Date].[Date].[10 Feb 2018].lag(13) : [Ship Date].[Date].[Date].[10 Feb 2018] ,[Measures].[Revenue] ) – SKenneth May 17 '18 at 16:04
  • If you cross join the time period set, inside the avg function with the target type, then that should limit the average to only that type – whytheq May 17 '18 at 17:45
  • You could up the answer if you like ;) – whytheq May 17 '18 at 20:30