1 Reply Latest reply on Aug 20, 2018 4:14 AM by Okechukwu Ossai

    How can I calculate comparison of the period to max of selected year and the last year

    Jiwon Kye

      Hi, everyone. help me for making a calculate field.

       

      I've created two parameters. one shows year, the other shows month.

       

      If I click on January 2017, compared with January 2016.

       

      If you select all of the month parameter, you will need to compare the sum from January ~ June 2017 to January ~ June 2016.

       

      because the maximum value for 2017 is April.

       

      How can I this problem?

       

      I've attached Sample dashboard. pliz help me.

       

        • 1. Re: How can I calculate comparison of the period to max of selected year and the last year
          Okechukwu Ossai

          Hi Jiwon,

           

          I've modified the Sample Superstore dataset so that Year 2017 ends in April.

           

          Step 1: Correct the typographical error in your Year parameter. Change 207 to 2017.

           

          Step 2: Create a calculated field [Minimum Month]

          MIN({FIXED : MAX(IF YEAR([Order Date]) = INT([Parameters].[Year]) THEN DATEPART('month', [Order Date]) END)},

              {FIXED : MAX(IF YEAR([Order Date]) = INT([Parameters].[Year])-1 THEN DATEPART('month', [Order Date]) END)})

           

          Step 3: Create a calculated field [Date Filter]

          IF [Month] <> 'All' THEN

              IF YEAR([Order Date]) = INT([Parameters].[Year]) OR YEAR([Order Date]) = INT([Parameters].[Year])-1 THEN

                  IF DATEPART('month',[Order Date]) = INT([Month]) THEN 1

                  ELSE 0 END

              ELSE 0 END

          ELSE

              IF YEAR([Order Date]) = INT([Parameters].[Year]) OR YEAR([Order Date]) = INT([Parameters].[Year])-1 THEN

                  IF DATEPART('month',[Order Date]) <= [Minimum Month] THEN 1

                  ELSE 0 END

              ELSE 0 END

          END

          Right click on the field name and select convert to discrete. Add [Date Filter] to the filter shelf and set to '1'.

           

          Hope this helps.

          Ossai