Search code examples
mdxolapmondrian

How to get avg from date range?

I have a Date dimenstion with levels: Year, Month, Day. I am need to get average by month for range like this [Date].[2011].[1].[10]:[Date].[2011].[10].[20]

Solution

  • Something along these lines:

    AVG(
  EXISTS(
    [Date].[Month].MEMBERS
    ,[Date].[2011].[1].[10]:[Date].[2011].[10].[20]
  )    
)

    Or if you want the daily average:

    AVG(
    [Date].[2011].[1].[10]:[Date].[2011].[10].[20]   
)

    Or is you want the average by the count of number of days for the range you specified:

    DIVIDE(
   SUM([Date].[2011].[1].[10]:[Date].[2011].[10].[20])
  ,EXISTS(
    [Date].[Month].MEMBERS
    ,[Date].[2011].[1].[10]:[Date].[2011].[10].[20]
   ).count
)