2 Replies Latest reply on Nov 21, 2017 12:07 PM by Wes Reneau

# LOD Calculation - Find Previous Year Ending Value

Hi Friends,

I'm trying to write a calculated field that returns the previous fiscal year ending value to create a Net Change calculation. In my scenario June is the last month of the fiscal year. It is the 'starting' point for any growth that happens in July through June in the next FY. I cannot use a quick table YTD Growth calculation because this is used in a sheet that has a month quick filter already applied. I was attempting to create a FIXED LOD, but can't seem to get it to work.

For example, ending numbers for each month are:

June FY17 = 100

----

July FY18 = 110

Aug FY18 = 105

Sep FY18 = 125

...

If I wanted to find the FYTD Net Change as of September the calculation would be

(125-100)/100 = 25% increase

This is my attempt and it gives me a bogus number. I would expect it to return 100.

{FIXED [Country Name], [AS_OF_DATE]>=Date('06/1/2017') AND [AS_OF_DATE]<=Date('06/30/2017'):Sum([Total Children])}

Any ideas on what I'm doing wrong within my calculation?

Thanks,

Wes

• ###### 1. Re: LOD Calculation - Find Previous Year Ending Value

Make a [Fiscal Year] dimension.

IF Month([Date]) >6 then YEAR([Date])+1 ELSE YEAR([Date]) END

So every row will have a fiscal year value.  Jan-June will have the value of its own year.  But Jul-Dec will be in the next fiscal year.

Then you can put [Fiscal Year] on the sheet and you can know what the max value for any fiscal year was that way.

If you need help doing this, a sample workbook would help so that I can show you.

• ###### 2. Re: LOD Calculation - Find Previous Year Ending Value

Hey Joe,

Thanks for the suggestion, it pointed me in the right direction. Here's what I did....

Created calculated field named 'Fiscal Year - Calc'

if MONTH([AS_OF_DATE]) > 6 then

YEAR([AS_OF_DATE]) + 1

else YEAR([AS_OF_DATE])

end

Created 'LOD Prev Fiscal Year Ending Value'

{ FIXED [Fiscal Year - Calc], [Partner Name]:SUM([Audit Actuals])}

I used the second calculated field within a tooltip so i could use it within the sheets title.

Works like a charm!

Thanks!