1 2 Previous Next 17 Replies Latest reply on Jul 29, 2019 12:53 PM by Kelly McGrady

# YoY Calc

Afternoon,

Ok so I am trying to create a Year over Year calculation for my data.  I started my journey this morning by watching Any Krebel's video on YoY calcs  - which is awesome!

This would work great except there is one problem for me - this calculation makes the assumption that a year is traditional beginning in January and ending in December.  Unfortunately, that is not my case.  I have a fiscal year to work with that begins in Oct and Ends in September.  For example - 2018: Oct 2017 - Sep 2018; 2019: Oct 2018 to Sep 2019

So how can I make a YoY calc and taking into account the fiscal year?

Attached is a sample of my data and I am using Version 2018.1

Thank you,

Scott

• ###### 1. Re: YoY Calc

Scott, please see this image here. You will need to set the fiscal year start month on the date field that you are using in the calculation. Let me know if this works.

• ###### 2. Re: YoY Calc

John,

Thanks for responding.  I agree that using that function will transform Oct 2018 into FY 2019.

The problem is that the calculation done as per Andy video which says: {MAX(YEAR([Trans Date (Final)]))} ; still sees the year as the year with out the fiscal year compensation.  In other words Oct 2018 is still Oct 2018 and excluded.

Thanks,

Scott

• ###### 4. Re: YoY Calc

Not sure what you have made there.   What I am looking for is to be able to compare the percentage of difference from where I am at one point in 2019 to the same point I was at in 2018 and to be able to measure the difference in a percentage of change.

• ###### 6. Re: YoY Calc

Hi,

Is this something your looking for

To calculate the current value

IF

FY Order Date <= Latest Date AND

FY Year = DATEPART("year", Latest Date ) AND

(int(((month(FY Order Date))%12)/3)+1) = DATEPART("quarter", Latest Date ) AND

DATETRUNC( "month", FY Order Date) = DATETRUNC("month", Latest Date)

THEN

value

END

To calculate the previous value:

IF

Dayofyear <= DATEPART('dayofyear',Latest Date)+31

AND

YEAR(FY Order Date) = YEAR(Latest Date)-1

AND

MONTH(FY Order Date) = MONTH(Latest Date)

THEN

Value

END

dayofyear:

IF datepart('month',FY Order Date) > 11

THEN

DATEDIFF('day',

MAKEDATE(

year(FY Order Date),

12,

1)

,FY Order Date)

+1

ELSE

DATEPART('dayofyear',FY Order Date)

+ 31

END

Kindly note that I assumed my financial year is from Dec - Nov and the latest date in the calculation indicates the max date present in the dataset.

• ###### 7. Re: YoY Calc

No not really as I am looking for a solution that does not involve using a table calc.  I am looking for something that will allow me to see the YoY percentage change and then look at the monthly detail as needed.

• ###### 8. Re: YoY Calc

Swarnalatha,

Wow!  I have to tell you that I am having a hard time understanding what you have suggested here and making the jump from there to my data.  Sorry!

• ###### 9. Re: YoY Calc

Hey I had the same issue and you can actually just right click on the date pill and then and choose the month

• ###### 10. Re: YoY Calc

Kelly,

Thanks for responding.  I agree that using that function will transform Oct 2018 into FY 2019.  And this suggestion was the first suggestion.

The problem is that the calculation done as per Andy video which says: {MAX(YEAR([Trans Date (Final)]))} ; still sees the year as the year with out the fiscal year compensation.  In other words Oct 2018 is still Oct 2018 and excluded.

Thanks,

Scott

• ###### 11. Re: YoY Calc

IF MONTH([Date]) >= 10 AND YEAR([Date]) = 2017 THEN 2018

ELSEIF MONTH([Date]) < 10 AND YEAR([Date]) = 2018 THEN 2018

ELSE 2019 end

You can use this as the year maybe

1 of 1 people found this helpful
• ###### 12. Re: YoY Calc

How about constructing a new date where you map October to January , November to February ..

and then apply Andy's calculations on top of the new date?

Would that work? Thanks

• ###### 13. Re: YoY Calc

Kelly,

I sure appreciate you help!  I was heading this direction but I think I have been sitting here too long working on this and was going crazy until I read your response.

I modified what you said just at bit and came up with this:

IF MONTH([Date])>=10 THEN

MAKEDATE(YEAR([Date])+1, MONTH([Date]), DAY([Date]))

ELSE [Date]

END

Is sort of a deconstruction and reconstruction of the date into something that I can work with.

Thank you,

Scott

• ###### 14. Re: YoY Calc

Hey that's great ! happy to help

1 2 Previous Next