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
I don't see a question here. Have you tried this? What problems did you encounter? If you need assistance, please attach an example packaged workbook. If you have confidential data, please see Anonymize your Tableau Package Data for Sharing |Tableau Support Community.