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
)

     

     

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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'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
)