0 Replies Latest reply on Nov 3, 2013 7:50 PM by Felix Chou

    Analysis Services Date Filtering

    Felix Chou

      Has anybody experience relative date filtering issues in Tableau using SSAS? I tried with MDM and Tabular and both still struggle with implementing a functional relative date filter over a date hierarchy.

       

      I have search everywhere and only found 2 suggestions but netiher of the two can do the trick

      1. Create a Date calculated member pointing to the Date hierarchy using: [Date].[Calendar].CurrentMember.MemberValue
      2. Bring in a Non hierarchy Date dimension field and change to date type by right clicking

       

      The First option is the nearest but still has some problems. Let's say I want to filter 01-Jul-2013 to 15-Aug-2013:

      • If in Year level it returns nothing
      • If in Year -> Month (or Month only) it returns the full July and August (01-Jul-2013 to 31-Aug-2013)
      • if in Year -> Month -> Date (or Date only) it returns the expected results (01-Jul-2013 to 15-Aug-2013)

      Option ONE.png


      The second option is little worse. Let's say I want to filter 01-Jul-2013 to 15-Aug-2013:

      • If in Year level it returns correct result but in dreaded text or overlap if in charts
      • If in Year -> Month (or Month only) it returns correct result but in dreaded text or overlap if in charts
      • if in Year -> Month -> Date (or Date only) it returns the expected results (01-Jul-2013 to 15-Aug-2013)

      Option TWO.png

       

      Does anyone know any other solutions apart from the two options above where I can use relative date filter in a date dimension? One way to get around this is to design the dashboard as static date range so the end user cannot do any roll up aggregation/drill down but this defects the purpose of the self-service data visualisation.

       

       

      Thanks,

      Felix