Search code examples
ssasmdxolap

Limiting date range using Filter


Without using the colon operator how do I filter the set AllDates to just 05Jan2005 to 10Jan2006?

WITH 
  SET [AllDates] AS 
    [Date].[Date].[Date].MEMBERS 
  MEMBER [Measures].[DTkey] AS 
    [Date].[Date].CurrentMember.Member_Key 
  MEMBER [Measures].[DTmemValue] AS 
    [Date].[Date].CurrentMember.MemberValue 
  MEMBER [Measures].[DTvalue] AS 
    [Date].[Date].CurrentMember.Value 
SELECT 
  {[Measures].[DTmemValue]} ON 0
 ,Filter
  (
    [AllDates]
   ,
    [Measures].[DTmemValue] > 0
  ) ON 1
FROM [Adventure Works];

Solution

  • I don't have Adv Wks but tested the below on mine and it worked well.

    Is this what you're looking for?

    WITH 
      SET [AllDates] AS 
        [Date].[date].[date].members
      MEMBER [Measures].[DTkey] AS 
        [Date].[date].CurrentMember.Member_Key 
      MEMBER [Measures].[DTmemValue] AS 
       [Date].[date].CurrentMember.MemberValue 
      MEMBER [Measures].[DTvalue] AS 
        [Date].[date].CurrentMember.Value 
    SELECT 
      {[Measures].[DTmemValue]} ON 0
     ,Filter
      (
        [AllDates]
       ,
       CDate([Measures].[DTmemValue]) >= CDate("01/05/2005") 
       and CDate([Measures].[DTmemValue]) <= CDate("01/10/2006")
      ) ON 1
    FROM [Adventure Works];