6 Replies Latest reply on Feb 13, 2019 10:28 AM by Joe Oppelt

# Calculate % difference between 2 sheets which each use Relative Date

I have a dashboard where I use 2 sheets having the same values but differ in the Relative date filter used for each sheet. I need a new sheet to calculate the percent difference between the two.

The reason why I can't keep both bars in the same sheet is that I need both bars to be filtered according to the Relative Date filter separately according to the users wish.

I am not aware of any calculated field I could use which could understand to calculate the value according to the relative date for that sheet .. please help!

attached workbook for an example: • ###### 1. Re: Calculate % difference between 2 sheets which each use Relative Date

A calc's value that depends on a sheet's filter is done in the context of that sheet.  You're not going to get values derived in one sheet to be visible to another sheet.

Change those filters to parameters.  Two different parameters.  Make parameter values of "Previous Year" and "Last 3 quarters" and whatever other values you want the user to select from.  You can still have two different sheets to display things, but create a calc that manages the ranges for the respective sheets.

[Start Date for Range1]

IF [Sheet 1 Parameter] =  "Last 3 quarters" then

ELSEIF [Sheet 1 Parameter] = "Current Year" then

DATEFRUNC('year', TODAY())

...

Etc.

END

And then make a final filter calc that says

SUM( IF [Date] >= [Start Date for Range1] then [Sales] END )

If you manage the sheets that way, you can make calcs that can "see" both bars because you can re-calc both bars in either sheet.  (And I you wanted, you could actually display both bars in one sheet!)

1 of 1 people found this helpful
• ###### 2. Re: Calculate % difference between 2 sheets which each use Relative Date

Thank you! I am trying that as of now, could you share what would be the formulas used for YTD, Previous YTD, Previous Year?

• ###### 3. Re: Calculate % difference between 2 sheets which each use Relative Date

YTD is what I did earlier in "Current year".

DATETRUNC('year',[some date]) creates a value of Jan/1/(year of the input date)

In all of my example I was assuming that you were summing from the start date up to TODAY().  If you want to start at a given date and end at a date other than today, you'll also have to make a calc for [End Date].  In many cases the end date will be today().  But if you want to do prior year from jan 1 through the same date as today but a year ago, you can do something like:

If you did that today, your result would be Feb/12/2018.

So you would just have to manage both Start and End dates for both ranges, and grab the right rows to sum up.

And therefore if you have an end date in the equation, your final sum would look something like this:

SUM( IF [Date] >= [Start Date for Range1] and [Date] <= [End Date for Range 1] then [Sales] END )

• ###### 4. Re: Calculate % difference between 2 sheets which each use Relative Date

It's a cool date function.  You can trunc to the beginning of the current week, month, quarter or year.

You can get all the date functions here:

DATETRUNC('month', TODAY())  on Feb 12, 2019 would result in a value of 2/1/2019,

DATETRUNC('week',TODAY()) would give you 2/10/2019.

Etc.

You can nest that syntax inside the DATEADD function as I did in my earlier example.

• ###### 5. Re: Calculate % difference between 2 sheets which each use Relative Date

Thank you. I am struggling a bit with getting the end date range as well.

Basically, I would like the user to have the following options on each bar:

Current Full Year Totals

Previous Full Year Totals

YTD (1/1/2019 - 2/13/2019 (TODAY))

Previous YTD (1/1/2018 - 2/13/2018)

• ###### 6. Re: Calculate % difference between 2 sheets which each use Relative Date

End Date:

DATE(CASE [Period range #1]

when "Previous Year" then  DATETRUNC('year', TODAY())-1

//  ^^ truncate to the first of current year,

// then subtract one day to last day of prior.

when "Previous YTD" then DATEADD('year', -1,  TODAY())

when "YTD" then  TODAY()

when "Current Year" then makedate(year(today()), 12, 31)

END)

// wrapping this calc in the DATE() function makes it a DATE field instead

//  of a date-time field

Start Date:

DATE(CASE [Period range #1]

when "Previous Year" then DATEADD('year', -1, DATETRUNC('year', TODAY()))

when "Previous YTD" then DATEADD('year', -1, DATETRUNC('year', TODAY()))

when "YTD" then DATETRUNC('year', TODAY())

when "Current Year" then DATETRUNC('year', TODAY())

END)

// wrapping this calc in the DATE() function makes it a DATE field instead

//  of a date-time field

I typed these off the top of my head, so some syntax or logic corrections might be needed when you implement these.