3 Replies Latest reply on Mar 25, 2016 6:45 AM by Shinichiro Murakami

    Calculating Prior Year

    Lindley Torbenson

      I would like to calculate a Prior Year measure that will display in the current year (without the need to display the prior year).  I need this measure to show the same level of detail as the other numbers in the table.

       

      I currently have 2 measures (Actuals & Budget) that I am displaying in my table.  I have created a calculated variance to compare them.  I would like include the Prior Year and perform the same variance calculations against prior year.


         

      Account HeaderActualPrior Year ActualBudgetVariance to PY ($)Variance to PY (%)Variance to Budget ($)Variance to Budget (%)
      Sales$870,572.74$739,683.28$721,077.26$130,889.4517.7%$149,495.4820.7%
      Cost of Goods Sold-$2,718.59-$2,525.25-$2,295.61-$193.347.7%-$422.9818.4%
      Payroll & Benefits-$55,102.24-$45,707.19-$72,719.28-$9,395.0520.6%$17,617.04-24.2%
      Expenses-$46,262.87-$39,567.41-$45,790.36-$6,695.4616.9%-$472.511.0%
      Total$766,489.04$651,883.44$600,272.01$114,605.6017.6%$166,217.0227.7%
        • 1. Re: Calculating Prior Year
          Shinichiro Murakami

          Here you go,

           

          Duplicate Data Source

          On second data source (Orange), Duplicate "Date ID"

           

          Rename "Date ID" to "Date ID original"

           

          Rename "Date ID(copy)" to "Date ID"  // for the purpose of auto data relationships

           

           

          On primary Data source,

          Create two new calculatd fields.

          [Var to PY ($) ]

          SUM([Actual])-SUM([Sample Data (SampleData_PriorYear) (copy)].[Actual_Prev_Year])

           

          [Var to PY (%)]

          [Var to PY ($) ]/SUM([Sample Data (SampleData_PriorYear) (copy)].[Actual_Prev_Year])

           

          Thanks,

          Shin

           

          9.2 attached.

          1 of 1 people found this helpful
          • 2. Re: Calculating Prior Year
            Łukasz Majewski

            Hi,

             

            All is fine with this sample dataset but be careful. This technique only works as long as the primary source covers all potential combination of dimensions used in the view. If for example in the current period there is no values in a certain category on which data is blended but it exists in previous year's period then that PY data will be filtered out.

            Also your wb extract doubles in size which in some cases may also be a concern. There are alternative ways - the attached shows the same using table calculations.

             

            Edited: modified date filter a bit and removed the redundant year dimension and grand totals added:

            • 3. Re: Calculating Prior Year
              Shinichiro Murakami

              Thank you for following up, Lulasz.

               

              Shin