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

    YTD for each month

    Med Ouza



      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




            • 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
              I can display
              NameYTD (02/15)
              But i wanted to show :
              January (ytd)February (ytd)March(ytd)
              • 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.