Step 1: Create two formula fields using the Sales (Ordered) dataset

To calculate the year over year variance, you need to split the sales amount into two separate fields in the dataset. You can create the two following formula fields:

• Amount LYTD- The Amount Last Year to Date field returns the sales amounts from all of last year up until today's date.

• Amount TYTD- The Amount This Year to Date field returns the sales amounts for transactions created from the beginning of this year to today's date.

Step 2: Create your pivot table

Set the following fields to the corresponding section of the Layout panel:

• Rows: Sales Rep

• Columns: None

• Measures: Amount TYTD (Sum) and Amount LYTD (Sum)

Step 3: Create your calculated measure

Use the following definition:

(Amount TYTD (Sum) - Amount LYTD (Sum)) / Amount LYTD (Sum)

Step 4: Format the results

You can also customize your pivot table results, add totals and grand totals, or set the decimal values to percentage values.