Skip to Content
0
Jun 19, 2018 at 02:40 PM

Time period comparison determined by parameter

149 Views

I am trying to produce a report that compares corresponding periods within a date range parameter. Currently, I run the report each week and would like to have the new report make the following comparisons:

  • Last week to the previous week
  • Current month to the same month last year over the same period, determined by the parameter.
  • Current year to two previous years over the same period, determined by the parameter.

Right now, I am using running totals with the formulas below.

Current year: {@Formula} in ["Type"] and (Year({date.field})=Year(CurrentDateTime))

Previous year:{@Formula} in ["Type"] and ({date.field} in LastYearYTD)

I have also set up a similar LastYearMTD formula for the month-to-month comparison. The problem I'm having is that the end dates are determined by the current date with the YTD function rather than over the date range I've entered into the parameter. So when trying to compare the period of 1/1/2018-6/10/2018 to the same period in 2017, it is currently comparing it to 1/1/2017-6/19/2017.