Forum Discussion

jps_HHH's avatar
jps_HHH
Helper II
1 year ago
Solved

Accumulate sum

Hi all,   I've a database that it is a list of several costs.  Some lines are related to budget items and other lines are the atual costs debited.  (there is a column with BGD if it is an item rel...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi jps_HHH ,
    here's an example of how to calculate a forecast assuming a percentage growth:

    ForecastActualCosts =
VAR _maxActualDate = CALCULATE(
    MAX('Cost Production Lines'[Month]),
    'Cost Production Lines'[Type] = "ACT"
)
VAR _futureMonths = SELECTCOLUMNS(
    FILTER(
        ALL('Cost Production Lines'[Month]),
        'Cost Production Lines'[Month] > _maxActualDate
    ),
    "Month", [Month]
)
VAR _forecastValue = 1.05 -- Example: 5% monthly growth
RETURN
SUMX(
    _futureMonths,
    CALCULATE(
        SUM('Cost Production Lines'[Val.in rep.cur.]) * _forecastValue,
        'Cost Production Lines'[Month] <= _maxActualDate
    )
)

     

    Modify _forecastValue or replace the growth logic with your specific forecast method.

     

    Now you can combine Actual and Forecast Lines:

    CombinedActualAndForecast =
VAR _maxdate = MAX('Cost Production Lines'[Month])
VAR _actualMaxDate = CALCULATE(
    MAX('Cost Production Lines'[Month]),
    'Cost Production Lines'[Type] = "ACT"
)
RETURN
IF(
    _maxdate <= _actualMaxDate,
    CALCULATE(
        SUM('Cost Production Lines'[Val.in rep.cur.]),
        ALLSELECTED('Cost Production Lines'),
        'Cost Production Lines'[Month] <= _maxdate,
        'Cost Production Lines'[Type] = "ACT"
    ),
    ForecastActualCosts
)

     

     

jps_HHH's avatar
jps_HHH
Helper II
1 year ago

It works. 

But the "actual line" should stop at November and not maintain the same values for future months. And I would also like to include a forecast for "actual line" . If it will overlap the budget line in March 2025 or not. 

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi jps_HHH ,
here's an example of how to calculate a forecast assuming a percentage growth:

ForecastActualCosts =
VAR _maxActualDate = CALCULATE(
    MAX('Cost Production Lines'[Month]),
    'Cost Production Lines'[Type] = "ACT"
)
VAR _futureMonths = SELECTCOLUMNS(
    FILTER(
        ALL('Cost Production Lines'[Month]),
        'Cost Production Lines'[Month] > _maxActualDate
    ),
    "Month", [Month]
)
VAR _forecastValue = 1.05 -- Example: 5% monthly growth
RETURN
SUMX(
    _futureMonths,
    CALCULATE(
        SUM('Cost Production Lines'[Val.in rep.cur.]) * _forecastValue,
        'Cost Production Lines'[Month] <= _maxActualDate
    )
)

 

Modify _forecastValue or replace the growth logic with your specific forecast method.

 

Now you can combine Actual and Forecast Lines:

CombinedActualAndForecast =
VAR _maxdate = MAX('Cost Production Lines'[Month])
VAR _actualMaxDate = CALCULATE(
    MAX('Cost Production Lines'[Month]),
    'Cost Production Lines'[Type] = "ACT"
)
RETURN
IF(
    _maxdate <= _actualMaxDate,
    CALCULATE(
        SUM('Cost Production Lines'[Val.in rep.cur.]),
        ALLSELECTED('Cost Production Lines'),
        'Cost Production Lines'[Month] <= _maxdate,
        'Cost Production Lines'[Type] = "ACT"
    ),
    ForecastActualCosts
)