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 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? 

 

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
)

     

     

4 Replies

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

         

         

  • Laxmanjatoth's avatar
    Laxmanjatoth
    Resolver I
    1 year ago

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