1 Reply Latest reply on May 17, 2016 7:22 PM by Bill Lyons

# Dividing values with 2 different date ranges.

Hello,

I am working on an Executive db of financial ratios. One of the ratios is "return on assets"....Both are EBITDA and Total Assets are Calculated fields.

EBITDA / Total Assets

EBITDA - would use a prior rolling 12 month range

Total Assets - Would use the last date in the range.

and I'm always calculating it at the end of a month....

So, if I want to calculate the "Return on Assets" for 3/31/2016

EBITDA (would be monthly EBITDA from April 1, 2015 - March 31, 2016

Total Assets (@ 3/31/2016)

So, if I want to calculate the "Return on Assets" for 1/31/2016

EBITDA (would be monthly EBITDA from February 1, 2015 - January 31, 2016

Total Assets (@ 1/31/2016)

My thoughts are to create a Calculated Field on 12 Month Rolling EBITDA and a Prior Month Total Assets and then divide the 2.

Prior Month Total Assets Calculated Field:

IF [Dates]=DATETRUNC('month',TODAY())-1  THEN [Total Assets]  ELSE 0 END

EBITDA (calc field is).....       [Monthly Net Income]+SUM([Monthly Interest])+SUM([Monthly Taxes])+SUM([Monthly Depreciation])+sum([Monthly Amortization])

Total Assets (calc field is)....CASE [Account Type] WHEN 'ASSETS' THEN [Ending Balance] ELSE 0 END