4 Replies Latest reply on Nov 4, 2015 5:37 AM by Daniel Vincent

    YTD for each month

    Med Ouza

      Hi,

       

      I've done YTD as following :

       

      Create a parameter with [date_choice]

      Create calculated fieldYTD :

      IF [Book Date] <= [date_choice] AND DATEDIFF('year',[Book Date],date_choice])= 0 THEN [Actual] END

       

       

      This work fine if I want to display only a YTD of a month - I only pick in the column Year(Book Date)

       

      BUT as soon as I divide it into month, it display then the MTD (cauz the start date is the current Month)

       

      How I can do in the formula that the start date is 01/01/xxx if the current year ? In order to see each YTD of each month

       

      Thanks for your help

        • 1. Re: YTD for each month
          Nicholas Hannan

          Hi Med,

           

          My understanding of your task is that, if you select a date with [date_choice], you want to get a total of your measures from the first day of that month to that day.

           

          If this is correct, try this:

           

          IF [Book Date] <= [date_choice] AND [Book Date] >= DATETRUNC('month', [date_choice]) THEN [Actual] END

           

          If this does not solve the problem, please attach a sample workbook. As much as I can suggest ways to get a MTD, the way you need to do it will depend on how you are arranging the data in your workbook.

           

          Best regards.

          1 of 1 people found this helpful
          • 2. Re: YTD for each month
            Rody Zakovich

            Hello Med,

             

            I built an example workbook that discusses what you are looking for. It has multiple parameters that all the user to swap between YTD, QTD and MTD based on a parameter date.

             

            Current vs Previous Period to Date Comparison

             

            I hope this helps

             

            Regards,

            Rody

            • 3. Re: YTD for each month
              Med Ouza

              Sorry to reopen this but I'm faced again to the same issue.

               

              I can have a YTD of a defined date but I can't display at the same time the YTD corresponding for each month . Because with YTD formula, as soon as I put month into the column shelf, it's doing the MTD automatically.

               

              To illustrate this, my data are like this :

                 

              NameValueBook date
              A1031/01/2015
              A1030/02/2015
              A1030/02/2015
              A1031/03/2015
              I can display
              NameYTD (02/15)
              A30
              But i wanted to show :
              January (ytd)February (ytd)March(ytd)
              103040
              • 4. Re: YTD for each month
                Daniel Vincent

                A little clarification needed.

                 

                Do you want the YTD value to show in each month to be the same?   Do you want a running YTD total for each month?

                 

                It would greatly help us to post a package workbook example with what you expect for the months.