Accumulate sum

[jps_HHH]

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 related to budget or ACT if it is a cost that was debited). 

I created a new measure to calculate the accumulated sum using this formula:

**bleep** Atual  =

VAR _maxdate = MAX('Cost Production Lines'[Month])

RETURN

CALCULATE(sum('Cost Production Lines'[Val.in rep.cur.]),ALLSELECTED('Cost Production Lines'),'Cost Production Lines'[Month]<= _maxdate)

And I created a visual (line graph) to show the accumulated costs over the months. To avoid to show "budeget" items, I filtered the visual to not show BDG items). 

However, I would like to add a new line showing the accumulated budget over the months too. Is it that possible? 

[Laxmanjatoth]

Accumulated Budget =
VAR _maxdate = MAX(date)
RETURN
CALCULATE(
SUM('Cost Production Lines'[Val.in rep.cur.]),
ALLSELECTED('Cost Production Lines'),
'Cost Production Lines'[Month] <= _maxdate,
'Cost Production Lines'[Type] = "BGD"
)

[Bibiano_Geraldo]

Hi @jps_HHH ,
You already have this measure, but ensure it filters only for ACT:

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

Now create a similar measure but filter for BDG:

AccumulatedBudgetCosts =
VAR _maxdate = MAX('Cost Production Lines'[Month])
RETURN
CALCULATE(
    SUM('Cost Production Lines'[Val.in rep.cur.]),
    ALLSELECTED('Cost Production Lines'),
    'Cost Production Lines'[Month] <= _maxdate,
    'Cost Production Lines'[Type] = "BDG"
)

Add Month to the X-axis.
Add both AccumulatedActualCosts and AccumulatedBudgetCosts to the Values field of the line chart.
This will create two separate lines: one for the actual costs and one for the budget costs.

If your data model allows, you can use a Type column (ACT/BDG) as a legend. This will automatically split the lines by type, but only if you don't need separate measures.

[jps_HHH]

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 · Accepted solution]

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
)