2 Replies Latest reply on Dec 27, 2012 1:03 PM by Robert Royer

    YTD Calculation

    Ajit Kumar

      Scenario – I am creating a Dashboard which is a combination of 7 reports. I have given a filter of Month which is a Global filter in the Dashboard since it is a monthly report. One of the report out of the 7 report is a Table, where I am giving current month number and YTD(Year till date) number.

                 

      Query –

       

      • ·         As I said there is a Global filter of Month in the Dashboard, YTD number should be calculated on the basis of Month filter selection. For example – Suppose current month is July, then the YTD number will be Addition of  Jan till July. But once the user change the filter form July to March, then the YTD number should be the addition of Jan till March. How to achieve this calculation?
      • ·         Is it possible to create in a single sheet (Month and YTD in a single sheet) ?

       

      Please suggest the way around.

        • 1. Re: YTD Calculation
          Zubair Aleem

          I have a similar issue today! I was told by my developer the only way around this is by using parameters. As a global filter filters the data at source connection

          • 2. Re: YTD Calculation
            Robert Royer

            Parameters are definately the easiest and most flexible option.

            Let's call the parameter ReportDate.

            Let's also call your measure (the one you want totaled) Sales, and your record date measure SaleDate.

             

            You can create two calculated fields MTD and YTD.

            MTD = IF str(month([SaleDate]))+'-'+str(year([SaleDate])) = str(month([ReportDate]))+'-'+str(year([ReportDate])) THEN [Sales] ELSE 0 END

             

            YTD = IF str(year([SaleDate])) = str(year([ReportDate])) THEN [Sales] ELSE 0 END

             

            You can then pull those two fields in (MTD and YTD) where you formerly pulled [Sales]. This allows you to view MTD and YTD on the same row/table.

             

            You can also use your parameters to filter other worksheets (in place of the global filters). If you need help with this, let me know.