2 Replies Latest reply on Mar 18, 2016 6:48 AM by Andrew Watson

    Filter full calendar months

    Alex Zielinski

      Hey, so I'm trying to create a filter that would give me full months.

       

      I'm using a parameter to let me chose between "this month", "last month", "2 months ago", etc.

       

      I don't want a running month, but a full month from the first day of the month until the last.

       

      How do I go about that?

       

      Thanks!

        • 1. Re: Filter full calendar months
          Shinichiro Murakami

          Alex,

           

          Here you go.

          Create Parameter as below

          Create calculated field.

           

          [Date_Display]

          if month(dateadd('month',int([Select param]),today()))=month([Date]) 

          and year(dateadd('month',int([Select param]),today()))=year([Date])

          then [Date] END

           

          Put [Date_Display] in filter.

           

           

          Thanks,

          Shin

           

          9.0 attached

          • 2. Re: Filter full calendar months
            Andrew Watson

            You could create a number of calculated fields, which will return true or false for the time period, such as:

             

            CurrentMonth: MONTH([DateField) = MONTH(TODAY()) AND YEAR([DateField) = YEAR(TODAY())

            PreviousMonth: MONTH([DateField) = MONTH(DATEADD('month',-1,TODAY())) AND YEAR([DateField) = YEAR(DATEADD('month',-1,TODAY()))

            etc for your time periods

             

            Then create another calculated field, [MeasureByPeriod]:

             

            IF [PARAMETER] = 'this month' AND [CurrentMonth] = TRUE THEN [Measure]

            ELSEIF [PARAMETER] = 'last month' AND [PreviousMonth] = TRUE THEN [Measure]

            etc

            END

             

            This new field will show your measure value for the time period selected in your parameter.

            1 of 1 people found this helpful