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

    Dividing values with 2 different date ranges.

    Michael Franz

      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