MDX code - Aggregate with Lag function not working

50 Views Asked by At

I'm trying to use the aggregate function but its returning like sum function, not sure what im doing wrong here. Ive tried to find a few examples online but nothing yet.

        WITH 
         MEMBER [Measures].[StartDate] AS
            Aggregate(@{Date!∅}.Lag(0), [Measures].[Net Sales])
         MEMBER [Measures].[7 Days] AS
            Aggregate(@{Date!∅}.Lag(7), [Measures].[Net Sales])
         MEMBER [Measures].[14 Days] AS
            Aggregate(@{Date!∅}.Lag(14), [Measures].[Net Sales])
         MEMBER [Measures].[21 Days] AS
            Aggregate(@{Date!∅}.Lag(21), [Measures].[Net Sales])
         MEMBER [Measures].[Avg 6-Mth Day] AS  
            (
               [Measures].[StartDate] + [Measures].[7 Days] + [Measures].[14 Days] + [Measures].[21 Days] 
            ) / 4
        
        SELECT
         NON EMPTY {      
            [Measures].[StartDate]
          , [Measures].[7 Days]
          , [Measures].[14 Days]
          , [Measures].[21 Days] 
         } ON "Columns"
         NON EMPTY [Calendar].[Hour].[Hour] ON "Rows"
        FROM 
         [ECRS Data Warehouse]
            WHERE 
             (@{Date!∅})
        
        

Expecting to get aggregate data by hour and not summed data by hour, its like its defaulting to sum not aggregate.

0

There are 0 best solutions below