2 Replies Latest reply on Jun 30, 2016 10:32 AM by Robert May

# YTD YoY Calculation Help - Why Does This Formula Not Calculate Ratios

I have found a few ways to calculate YTD and compare to the same timeframe of the previous year using a reference date parameter.

I used what I found to recreate a YoY YTD comparison down to the day and it is working great, with one issue.  This formula does not calculate multipled/divided measures.  It will add/subtract measures just fine.  Is there a reason for this?

Here is my calculation:

if DATEDIFF('year',[Date],[Reference Date])=1
and DATEPART('dayofyear',[Date])<DATEPART('dayofyear',[Reference Date])

then [Clicks]

ELSEIF DATEDIFF('year',[Date],[Reference Date])=0
and DATEPART('dayofyear',[Date])<=DATEPART('dayofyear',[Reference Date])

Then [Clicks]

END

and I used that for 3 measures - Clicks, Impressions, and Click Through Rate (Clicks/Impressions)

The CTR calculation looks the same:

if DATEDIFF('year',[Date],[Reference Date])=1

and DATEPART('dayofyear',[Date])<DATEPART('dayofyear',[Reference Date])

Then ([Clicks]/[Impressions])

ELSEIF DATEDIFF('year',[Date],[Reference Date])=0

and DATEPART('dayofyear',[Date])<=DATEPART('dayofyear',[Reference Date])

Then ([Clicks]/[Impressions])

END

Here is a screen shot:

Any clue why multiplication/division does not work?  Is there a fix for this?

Thank you

• ###### 1. Re: YTD YoY Calculation Help - Why Does This Formula Not Calculate Ratios

That's not the only thing not working.   Your Prev YTD Clicks are not correct either.

• ###### 2. Re: YTD YoY Calculation Help - Why Does This Formula Not Calculate Ratios

Sorry, I should have explained a little better. The code is meant to show clicks or impressions as long as it is within the Month/Day of the reference date.  Each YTD cell should match its adjacent cell, up until the same time as the reference date, regardless of year.

I changed the name of the column header to avoid further confusion I probably caused