Organic Growth Calculation

alfredmattos

I'm trying to calculate the organic growth. I know in order to do so, I'll need to determine the following:

Yr Ending Assets: CASE WHEN [REPORTING_CAL_DATE]=#2016-12-30#

  OR [REPORTING_CAL_DATE]=#2017-12-29#

   OR [REPORTING_CAL_DATE]=#2018-12-31#

   OR [REPORTING_CAL_DATE]=#2019-12-31#

   OR [REPORTING_CAL_DATE]=#2020-12-31#

   OR [REPORTING_CAL_DATE]=#2021-12-31#

OR [REPORTING_CAL_DATE]=#2022-3-31#

   THEN [Ending Assets] END

Prior-Year Assets: LOOKUP(SUM([Yr Ending Assets]),-1)

Organic Growth: SUM([Flows])/[Prior-Year Assets]

I'd like the end user to be able to see the organic growth for every subcategory. The data also has every month in between.

Please feel free to suggest a different way to calculate this. Thank you in advance.

GrantSmith

How is your data formatted? Is it summarized on the reporting date or are each date a snapshot of the values?

I'd recommend using a MagicETL dataflow to calculate the maximum data for each year (calculate the year with a formula tile YEAR(`date`) then pass it into a group by grouping on the year and select the max date value) then filter your original dataset to where those dates are the same. Then inner join back to your original dataset based on the year calculated field in both. This will get you the assest for that year. Then do another join to join your original dataset to the asset year join you just did but base it on the year - 1 (create a new calculated field and subtract 1 from the year and call it last_year) Now your dataset should have the prior year's assets for each month of reporting.