2 Replies Latest reply on Apr 25, 2017 4:27 PM by Okechukwu Ossai

    How to create a parameter to Choose 1 month in a date dimension that automatically pulls another month based on rules

    Adam Marinelli

      Hello,

       

      I would like to create a toggle or parameter/filter that allows me to choose a single month/year from a date dimension and which will automatically display an additional month, along with the month selected (two months prior). For example. If i've chosen January 2017 in the filter, i would like the table to also show November 2016. If i chose February 2017, show that month as well as December 2016....and so on....any suggestions?

        • 1. Re: How to create a parameter to Choose 1 month in a date dimension that automatically pulls another month based on rules
          Lisa Li

          Hello Adam,

           

          You can try the following calc:

          if [Date]=[Parameter] or dateadd('month',-2,[Parameter])=[Date] then [Value]

          else null

          end

           

          Then you can exclude nulls and only have those two months shown. Your parameter will have to return Date values for the dateadd function to work though.

           

          -Lisa

          CoEnterprise | Home

          1 of 1 people found this helpful
          • 2. Re: How to create a parameter to Choose 1 month in a date dimension that automatically pulls another month based on rules
            Okechukwu Ossai

            Hi Adam,

             

            You will need to use a parameter for this. I have broken the steps down into smaller codes for better understanding.

             

            Using the Sample Superstore data as an example, see the suggested solution below.

             

            1. Create a new date field [Date]

            DATETRUNC('month',[Order Date])

            This code truncates every date to the beginning of the month, thereby rolling up your dates into an aggregated month year format.

             

            2. Create a parameter [Select Date]

            From your requirement, you want this to be a single month/year selection. For this example, I have only made a string list for months in Year 2014.

            3. Create a calculated field [Selected Month]

            DATE(DATEPARSE("dd/MMM/yyyy", "01"+"/"+LEFT([Select Date],3)+"/"+RIGHT([Select Date],4)))

            This code converts the selected string parameter selection into a date.

            4. Create calculated field [Two Months Prior]

            DATE(DATEADD('month', -2, [Selected Month]))

             

            5. Create calculated field [Date Filter]

            IF [Date] = [Selected Month] THEN 1

            ELSEIF [Date] = [Two Months Prior] THEN 1

            ELSE 0

            END

            This code will ensure that only the selected month and prior 2 months are displayed. Put this on the filter shelf and set to 1 to filter out null date values.

             

            Just bear in mind that parameters are static and will not update automatically. You may consider having a separate Select Month and Select Year parameters to make it easier to for manual updates.

             

            See attached workbook. Hope this helps.

            Ossai

             

            1 of 1 people found this helpful