Insights - Compare Year To Date to Last Year to Date

Updated 11 months ago by Elena Yurkova

Compare Year To Date to Last Year to Date

You would like to build a widget that compares performance year to date vs. last year to date. However, last year to date always shows as a whole year that makes it impossible to compare apples to apples.

This article explains formulas behind these 4 widgets:

Widget 1. Year To Date

  1. To calculate year to date total add a widget filter 'Close Date' and set it to 'Today'
  1. Under 'Values' add the field you want to sum up, e.g. 'Corporate Revenue'.
  2. Apply YTDSUM function by going Quick functions to add Running Sum From Beginning of Year

  1. The end formula will look like the one below.
  1. Change the label from 'Yearly Total of...' to 'Year To Date' and adjust formatting if needed.

Widget 2. Last Year To Date

  1. Duplicate widget 1 or build it from scratch and go to 'Edit Formula'
  2. To calculate previous year to date values manually add PASTYEAR function on top of the existing formula:
  1. Change the label from to 'Last Year to Date'and adjust formatting if needed.

Widget 3. Compare This Year to Date to Last Year To Date, %

  1. Duplicate widget 1 or build it from scratch and go to 'Edit Formula'
  2. To calculate procentage change manually add GROWTHPASTYEAR function on top of the existing YTDSUM so your end result looks like this:
  3. Change the label to 'Comparison' and adjust formatting to per cent and add conditional formatting if needed.

Widget 4. Compare This Year to Date to Last Year To Date, $

  1. Duplicate widget 1 or build it from scratch and go to 'Edit Formula'
  2. To calculate difference of This Year To Date to Last Year To Date manually add DIFFPASTYEAR function on top of the existing YTDSUM so your end result looks like this:
  3. Change the label to 'Comparison' and adjust formatting to per cent and add conditional formatting if needed.


How did we do?