How to pull the annual total from the month forecast module?

Mili
Mili
in Modeling

Much appreciate if someone can help with this one.

Source module: monthly expense

list: cost center & expense type

Dimension: time (by month, with year total FY23); version (actual & forecast)

Line item: expense

Target module: Annual summary

list: cost center & expense type

Line item: expense, budget, variance, %

NO TIME AND VERSION DIMENSION.

Question: what is the correct formula for target module line item 'Expense'?

Tried

  1. TIMESUM(Source.Expense, TIME.'FY23'),
  2. SUM(Source.Expense, Source.Time.'FY23')
  3. Source.Expense[SELECT: TIME.'FY23']

None of them were working.

Tagged:

Best Answer

  • GauravKumar
    GauravKumar
    Answer ✓

    Hi @Mili your formula is not working because of Version. Which version you want to pull in target? Actual or Forecast? or the sum of both? If you want to pull Actual, use this formula:

    Source.Expense[SELECT: TIME.'FY23', SELECT: VERSIONS.Actual]

    Or else if you want to get the sum of Actual and Forecast then create a new version to aggregate the Actual and Forecast Versions

Answers

  • Matthewroberts
    Matthewroberts
    edited September 19

    Sum Function: If you're using software like Excel, use the SUM function to sum up the 12 monthly forecast values for the year. For example: =SUM(B2:M2) where B2 to M2 are your monthly forecast figures for the year. Reports & Dashboards: If you're using a more sophisticated system like a business intelligence tool or an ERP system, there might be built-in reports or dashboards that provide annual totals derived from monthly forecasts. TellTims

  • Mili
    Mili
    edited September 18

    Thanks very much, @GauravKumar ! It works!!

Categories