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

    Dividing values with 2 different date ranges.

    Michael Franz



      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