3 Replies Latest reply on Dec 9, 2018 11:47 PM by Norbert Maijoor

    How to calculate YTD & Full Year ?

    Veera Veeru

      Hi,

       

      Can anyone help me with the below query ?

       

      I have a dimension called "Period" which contains "Jul'17 to Jun'18" and measure "Actual". Using this information, i would like to get MTD, YTD and Full Year, using calculations.

           

      Example: When I select "Mar'18" from the dimension "period" I should get the values of only "Mar'18" in MTD, and i should get the values added from "Jan'18" to "Mar'18" in QTD since they are in 3rd Quarter,  and  I should get the values added from "Jul'17" to "Mar'18" in YTD i.e. Mar'18 to selected month of a year.

       

      I need to use Parameter to select between MTD, YTD and FY.

       

      Thanks in Advance.

        • 1. Re: How to calculate YTD & Full Year ?
          Norbert Maijoor

          Hi Veera,

           

          Find my approach as reference below and stored in attached workbook version 10. located in the original thread.

           

          YTD.png

           

          1. Define paramter P1 Select Period

           

          p1 para.png

           

          2. Define P2 Select MTD/YTD/QTD/FY

           

          P2 para.png

           

          3. M1. FY:

          if DATEDIFF('month',[Period],[P1 Select Period])<=11

          and  DATEDIFF('month',[Period],[P1 Select Period])>=0 then [Actual] END

           

          4. M2. MTD: if DATEDIFF('month',[Period],[P1 Select Period])=0 then [Actual] END

           

          5. M3. QTD: if DATEDIFF('quarter',[Period],[P1 Select Period])=0 then [Actual] END

           

          6. M4. YTD:

          if DATEDIFF('year',[Period],[P1 Select Period])=0

          and [Period]<=[P1 Select Period]then [Actual] END

           

          7. Drag the required objects to the canvas and show parameter controls.

           

          Hope it helps,

           

          Regards,

          Norbert

          1 of 1 people found this helpful
          • 2. Re: How to calculate YTD & Full Year ?
            Veera Veeru

            Hi Norbert,

             

            Thanks you very much, This is what i am looking for, it really helped me a lot,

             

            One quick question: I would like to get FY data without considering period selection based on the data available in the source.

             

            for example: i have data 5k for full year, where as YTD i have only 4.5k. i am getting YTD as 4.5 but trying to get FY as 5K which should not consider my period/Period selection.

             

            Thanks,

            Veera

            • 3. Re: How to calculate YTD & Full Year ?
              Norbert Maijoor

              Hi Veera,

               

              Glad it worked out for you....& thanks for the "Badge". Appreciated. I am not sure if I understand your "One quick question". Could you elaborate once again.

               

              Regards,

              Norbert