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

# Calculating Prior Year

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 Header Actual Prior Year Actual Budget Variance to PY (\$) Variance to PY (%) Variance to Budget (\$) Variance to Budget (%) Sales \$870,572.74 \$739,683.28 \$721,077.26 \$130,889.45 17.7% \$149,495.48 20.7% Cost of Goods Sold -\$2,718.59 -\$2,525.25 -\$2,295.61 -\$193.34 7.7% -\$422.98 18.4% Payroll & Benefits -\$55,102.24 -\$45,707.19 -\$72,719.28 -\$9,395.05 20.6% \$17,617.04 -24.2% Expenses -\$46,262.87 -\$39,567.41 -\$45,790.36 -\$6,695.46 16.9% -\$472.51 1.0% Total \$766,489.04 \$651,883.44 \$600,272.01 \$114,605.60 17.6% \$166,217.02 27.7%
• ###### 1. Re: Calculating Prior Year

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

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

Thank you for following up, Lulasz.

Shin