ssasmdxssas-2008ssas-2012mdx-query

mdx query to calculate average between date range

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?

Solution

  • 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]
)