3 Replies Latest reply on May 17, 2017 9:14 AM by Norbert Maijoor

    When quarter closed show quarter / else show month

    maria.zumkehr

      Hi All

       

      My data source contains the whole year 2016.

      Now there will be added every ended month of 2017. So far until April 2017.

       

      To only show months which can be compared with 2017 I created a filter:

       

      IF MONTH([Date]) <= MONTH(DATEADD('month',-1,TODAY())) THEN 'until current month' ELSE 'show all' END

       

      The costs in 2016 should now be compared with the costs in 2017.

      Therefore I made two calculated fields:

       

      [Costs 2016]

      IF YEAR([Date]) = 2016 THEN [Costs] ELSE 0 END

       

      [Costs 2017]

      IF YEAR([Date]) = 2017 THEN [Costs] ELSE 0 END

       

      Always when a quarter has ended the costs in Q1 2016 should be compared with the costs in Q1 2017. But when a quarter is still running it should automatically compare month.

       

      For example:

      January 2017

      February 2017

      March 2017

      April 2017

      May 2017

       

      January, February and March should now be combined as Q1 and compared with Q1 in 2016. April and May should be compared not as quarter but as month (April 16 with April 17 and May 16 with May 17).

      When I add June 2017 it should automatically compare Q1 2016 with Q1 2017 and Q2 2016 with Q2 2017.

       

      The format should stay “date” as it should be possible to still down drill the quarters to month if wished so.

       

      Does anyone has an Idea how to do this?

       

      Thanks a lot for your help!

        • 1. Re: When quarter closed show quarter / else show month
          Norbert Maijoor

          Hi Maria,

           

          Find my approach as reference and stored in attached workbook version 10.1 located in the original thread

           

           

          1. Last month CY: if datediff('month',[Date],today())=1 then [Costs] END

           

          2. Last month PY: if datediff('month',[Date],today())=13 then [Costs] END

           

          3. Last Quarter CY: if datediff('quarter',[Date],today())=1 then [Costs] END

           

          4. Last Quarter PY: if datediff('quarter',[Date],today())=5 then [Costs] END

           

          5. Indicator:

          if datediff('month',[Date],today())=1 then "Last Month CY"

          elseif datediff('month',[Date],today())=13 then "Last Month PY"

          elseif datediff('quarter',[Date],today())=1 then "Last Quarter CY"

          elseif  datediff('quarter',[Date],today())=5 then "Last Quarter PY"  END

           

           

          Regards,

          Norbert

          1 of 1 people found this helpful
          • 2. Re: When quarter closed show quarter / else show month
            maria.zumkehr

            Hi Norbert

             

            Thank you very much for your help!

             

            Your approach was very useful. But when the year goes on it would get more and more complicated as there would be more measure values.

             

            You brought me to the idea, though, to do a calculated field with all possible outcomes called “Month 1-12 / Quarter 13-16”, just as you did it with your field “indicator”.

            This made it possible to finally group all dates as needed. Now the table groups automatically quarter by quarter and next to it month by month.

             

            With a field “Transformation Quarter / Month” I gave the before defined groups respective names. (Q1, April, Mai) ect.

             

            The Parameter “Reference Month” gives the possibility to go exactly one year back from the chosen month.

             

            You find my approach attached, as the formulas got quite long.

             

            Best regards,

            Maria

            • 3. Re: When quarter closed show quarter / else show month
              Norbert Maijoor

              Hi Maria,

               

              My feedback is "two-folded".

               

              a. Glad i could help you out getting the right direction":))

              b. I don't understand your remark "But when the year goes on it would get more and more complicated as there would be more measure values"

               

              The view of my approach will always be the same "Current Month CY vs Current Month PY" and "Last full Quarter CY vs Last full Quarter PY"

               

              If you need further explanation please let me know on return.

               

              Regards,

              Norbert