Search code examples
sql-serverssasmdxbusiness-intelligence

Aggregate between start date and date before last in MDX

Below member returns me Running Total between first and chosen date. It's possible to aggregate data up to one day/week/month before?

WITH 
MEMBER [Measures].[SUM] AS
AGGREGATE(
    NULL:TAIL(EXISTING [Date].[Date].[Date].Members).Item(0), 
    [Measures].[X]
)

Here is example (date can be a day, month, year...) :

DATE X SUM
------------
 1   1 NULL
 2   4  1
 3   2  5
 4   2  7
Solution

  • I think you've almost got it - to end the aggregation x number of days before you can use lag:

    WITH 
MEMBER [Measures].[SUM] AS
AGGREGATE(
    NULL
   :
    TAIL(
      EXISTING [Date].[Date].[Date].Members
    ).Item(0).lag(7) //<<<< finishes 7 days before chosen date
  ,[Measures].[X]
)