CODEWITHSUNDEEP
mdx
Daniel B
Daniel B

Reputation: 797

How to filter data in MDX for the last year

I'm trying to write an MDX expression which will return total invoiced for the last year (i.e. between today and 365 days back). I currently have:

SELECT 
    NON EMPTY 
      { [Measures].[Invoiced] } 
    ON COLUMNS 
FROM 
    ( 
     SELECT 
        ( 
          [Date Invoiced].[Day].&[2013-10-01T00:00:00] 
          : [Date Invoiced].[Day].&[2014-10-01T00:00:00] 
        ) ON COLUMNS 
      FROM [Sales]
    )

I would like to replace 2014-10-01T00:00:00 with something like Now(), and 2013-10-01T00:00:00 with something like (Now()-365)), but have been unable to find the correct syntax.

The MDX is to be used in PowerPivot in Excel 2010 which does not support parameters exposed to VBA, so I will not be able to substitute the dates programmatically.

Upvotes: 0

Views: 1811

Answers (1)

Raíza
Raíza

Reputation: 11

You can use the lag function on mdx, this function returns the value of a member n positions before the supplied member within a dimension.

Here is the reference for this function: http://mdxpert.com/Functions/MDXFunction.aspx?f=37

Here is how I use the lag for get the previous value from a dimension, in this case I got the value of the previous year. 

MEMBER [Measures].[Previous] as ([Data].CurrentMember.lag(12), [Measures].[Example])

Upvotes: 1

Related Questions