I’ve been dealing with this issue since March of this year. I know similar questiosn have been asked but I have not been able to solve it.

I have an excel sheet I am replicating so I know what values I am searching for. The set up is simple:

I have a data table where I need to calculate the state of charge of 5 different types of batteries. In excel I would :

For each scenario (there are 20 scenarios):

1. take the prior value, otherwise take the initial value (detailed below) and,
2. subtract the current dispatch; if the current dispatch is negative (ie charging) then multiply it by RTE of that specific kind of generator

I have tried multiple renditions of code, and I find accumulate rather hard to understand in practice.
If I could use mutate ( and I know that I cannot ) I would write something like this:

 Reservoir_dt <- Reservoir_dt %>%
   group_by(`Generator Name`) %>%
   mutate(Reservoir = Initial) %>%
   mutate(Reservoir = if_else(
     is.na(lag(Scenario)) | Scenario != lag(Scenario),
     Initial,
     lag(Reservoir) - pmax(`Dispatch (MW)`, 0) - (pmin(0, `Dispatch (MW)`) * RTE)
   )) %>%
   ungroup()

I’ve tried this but to no avail

Reservoir_dt %>%
  group_by(`Generator Name`,Scenario)%>%
  mutate(Reservoir= accumulate(
    `Dispatch (MW)`[-1],
    .init= if (is.character(Scenario[1])) Initial else Reservoir[1],
    ~ if (.y < 0) .x - .y * RTE else .x - .y
  )
  )
Error in `mutate()`:
ℹ In argument: `Reservoir = accumulate(...)`.
ℹ In group 1: `Generator Name = "Battery-100h"` and
  `Scenario = "Path1"`.
Caused by error:
! Invalid formula: '~if (.y < 0) .x - .y * RTE else .x - .y' 

 glimpse(Reservoir_dt)
Rows: 876,000
Columns: 10
$ `Generator Name` <chr> "PSH10h", "PSH20h", "Battery-…
$ DateTime         <dttm> 2050-01-01 00:00:00, 2050-01…
$ Scenario         <chr> "Path1", "Path1", "Path1", "P…
$ `Dispatch (MW)`  <dbl> 0.0000, 0.0000, 0.0000, -1104…
$ RTE              <dbl> 0.80, 0.80, 0.45, 0.85, 0.85,…
$ `Initial SOC`    <dbl> 0.5, 0.5, 0.5, 0.5, 0.5, 0.5,…
$ Duration         <dbl> 10, 20, 100, 2, 4, 10, 20, 10…
$ `Peak MW`        <dbl> 417.500, 208.750, 1544.280, 2…
$ Initial          <dbl> 2087.500, 2087.500, 77214.000…
$ NumericPath      <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…

(input data is merged but including it here if it helps clarify things)

input_res
Generator Name  RTE Initial SOC Duration  Peak MW   Initial
1:         PSH10h 0.80         0.5       10  417.500  2087.500
2:         PSH20h 0.80         0.5       20  208.750  2087.500
3:   Battery-100h 0.45         0.5      100 1544.280 77214.000
4:     Battery-2h 0.85         0.5        2 2540.610  2540.610
5:     Battery-4h 0.85         0.5        4  100.628   201.256

Reservoir_dt <- generation[`Generator Name` %in% input_res$`Generator Name`,
.( `Generator Name` = `Generator Name`,
`DateTime` = DateTime,
Scenario = Scenario,
`Dispatch (MW)`=`Dispatch (MW)`)]

# Merging reservoir with input data

Reservoir_dt<- Reservoir_dt[input_res,on = .(`Generator Name`)]

# Seting order since lagging values

Reservoir_dt[, NumericPath := as.numeric(gsub("Path", "", Scenario))]
Reservoir_dt<-setorder(Reservoir_dt, NumericPath, DateTime)

Extremely grateful for your help and detailed commentary

I have tried everything under the sun and used Chat gpt.
I have reviewed this answer, but i cannot get teh results i need:
How to produce a self-referencing variable in R (e.g., index levels given returns)?