I am doing a bar chart of ROIC per site e.g:
To annualise earnings, the calculation I need to use for a business unit with 3 months financial data is:
- (12/3) * sum(last 12 months EBITDA)
I need to get the 3 in the above formula (i.e. count of non blank EBITDA in last 12 months) into the layout of the ROIC chart so that I can do a rough annual forecast of EBITDA for sites less than 12 months old. I was wondering what the fastest way to do this is, the only rough ideas I have are:
- Use a 2nd dataview and divide EBITDA/EBITDA for all months in the current and prior financial year to convert non blank values to 1's. Then in another block sum this and use an if statement to max the value at 12. After this try using crossview to get the non blank EBITDA month count into the layout of the bar chart so that I can use it in the ROIC formula.
- Create a copy of the cube with EBITDA. Then create another cube that is EBITDA/EBITDA to convert values to 1's and then use this cube in the chart layout with the 'yearly moving total' function enabled with a cycle of 12.
I was wondering if there is a faster or easier way? Any ideas appreciated.