Problem with Circular Reference

Monpero
Monpero
edited June 2023 in Modeling

Hi I am building a module report and kept having a roadblock with circular referencing. Please what is a better workaround on this. I have tried to split the formulas into separate line items but kept getting circular reference.

a. Referencing Modules

b. Data source for Revenue

The build is below:-

1. Here is the module

2. Dimensions

3. Calculation

Day Count = 'clc06_Day Type by Month'.No of Days

Revenue = IF Actuals THEN 'dat00_HomePhoneUsage'.Revenue ELSE Day Count * Revenue Per Day

Revenue Per Day = IF Actuals THEN Revenue / Day Count ELSE PREVIOUS(Revenue Per Day * (1 + % MoM Change)

(Summary method type)

% MoM Change = IF Actuals Revenue Per Day / PREVIOUS(Revenue Per Day) ELSE 0

(Summary method type)

NOTE: % MoM Change line item (Formula Scope) will be applied so that the formula applies to Actuals only and Forecast is Input.

Best Answer

  • AkhilEmmanual
    AkhilEmmanual
    Answer ✓

    @innocent you can create a new line item which hold revenue values if its actual data and in Revenue Per Day line item you can refer that instead of revenue.Here in Revenue per day you are using revenue if its actual so there won't be any issue using Actual Revenue

    Actual Revenue - IF Actuals THEN 'dat00_HomePhoneUsage'.Revenue ELSE 0.

    Revenue = IF Actuals THEN 'dat00_HomePhoneUsage'.Revenue ELSE Day Count * Revenue Per Day

    Revenue Per Day = IF Actuals THEN Actual Revenue / Day Count ELSE PREVIOUS(Revenue Per Day * (1 + % MoM Change)

    Screenshot 2023-06-18 3.22.21 PM.png
    Screenshot 2023-06-18 3.23.16 PM.png

Answers

Categories