## Cumulative sum from a month ago until the current day for all the rows

I have a data.table with ID, dates and values like the following one:

DT <- setDT(data.frame(ContractID= c(1,1,1,2,2), Date = c("2018-02-01", "2018-02-20", "2018-03-12", "2018-02-01", "2018-02-12"), Value = c(10,20,30,10,20))) ContractID Date Value 1: 1 2018-02-01 10 2: 1 2018-02-20 20 3: 1 2018-03-12 30 4: 2 2018-02-01 10 5: 2 2018-02-12 20

I'd like to get a new column with the total cumulative sum per ID from a month ago until the current day for each row, like in the table below. NB: the third row is the sum of the second and the own third, because 2018-03-12 minus 1 month is greater than 2018-02-01, so we exclude the first row in the cum sum.

ContractID Date Value Cum_Sum_1M 1: 1 2018-02-01 10 10 2: 1 2018-02-20 20 30 3: 1 2018-03-12 30 50 4: 2 2018-02-01 10 10 5: 2 2018-02-12 20 30

Is there any way to achieve this using data.table?

Thank you!

Using `tidyverse`

and `lubridate`

, we first convert `Date`

to actual `Date`

object using `as.Date`

, then `group_by`

`ContractID`

and for each `Date`

`sum`

the `Value`

which is between current `Date`

and one month before the current `Date`

.

library(tidyverse) library(lubridate) DT %>% mutate(Date = as.Date(Date)) %>% group_by(ContractID) %>% mutate(Cum_Sum_1M = map_dbl(1:n(), ~ sum(Value[(Date >= (Date[.] - months(1))) & (Date <= Date[.])], na.rm = TRUE))) # A tibble: 5 x 4 # Groups: ContractID [2] # ContractID Date Value Cum_Sum_1M # <dbl> <date> <dbl> <dbl> #1 1 2018-02-01 10 10 #2 1 2018-02-20 20 30 #3 1 2018-03-12 30 50 #4 2 2018-02-01 10 10 #5 2 2018-02-12 20 30

**Cumulative total – DAX Patterns,** Inventory table that shows the stock of each product for every month, We want to create a measure that sums all the sales values up to a accumulates values from the beginning of time up to the current date. For example, in order to compute the running total while keeping the filter on the day of the� The goal is to dynamically sum values through a given number of months, hardcoded as 6 in cell G5. This is done by feeding a reference constructed with the OFFSET function into the sum function. The OFFSET function let's you build a reference using a starting point, a row and column offset, and a height and width.

This is largely a rolling sum question. `froll()`

would likely work but you'd have to complete the dataset first so that you can say how many days to roll backwards.

Here I do a non-equi self join. As data.table wants all fields generated before the join, I have to add a column `Dates_Lower = Dates-30`

so that I can complete the non-equi conditions. My chain with `last(Value)`

makes it work but I'm not always certain with these self-joins...

I also convert the Date to `as.Date`

and also renames it as `Date()`

is a base function.

library(data.table) dt <- data.table(ContractID= c(1,1,1,2,2) , Dates = as.Date(c("2018-02-01", "2018-02-20", "2018-03-12", "2018-02-01", "2018-02-12")) , Value = c(10,20,30,10,20)) dt[dt[, .(ContractID, Dates, Dates_Lower = Dates - 30, Value)] #self-join ,on = .(ContractID = ContractID , Dates >= Dates_Lower , Dates <= Dates ) , j = .(ContractID, Dates, Value) , allow.cartesian = TRUE ][, j = .(Value = last(Value), Cum_Sum_1M = sum(Value)) ,by = .(ContractID, Dates) ]

ContractID Dates Value Cum_Sum_1M 1: 1 2018-02-01 10 10 2: 1 2018-02-20 20 30 3: 1 2018-03-12 30 50 4: 2 2018-02-01 10 10 5: 2 2018-02-12 20 30

**Cumulative SUM per day, week and month - Databases,** I need to fetch the cumulative sum for sales made by a marketeer per day, week and month. from the weeks before but I also need the sales made per month, i.e. sales from the first day of the current week, which is a sunday, up to which means all days - and then you will always receive just one row� In the Apply Date Formatting dialog box, choose the month and year date format Mar-2001 that you want to use. See screenshot: 4. Click Ok to change the date format to month and year format, and then select the data range (A1:B15) that you want to work with, and go on clicking Kutools > Merge & Split > Advanced Combine Rows, see screenshot: 5.

This is an other working `data.table`

solution..

dt[, Date := lubridate::ymd( Date ) ] setkey(dt, Date) dt[dt, Cum_Sum_1M := { val = dt[ ContractID == i.ContractID & Date %between% c( i.Date - months(1), i.Date ), Value]; list( sum( val ) ) }, by = .EACHI ] setkey(dt, ContractID, Date)

**output**

# ContractID Date Value Cum_Sum_1M # 1: 1 2018-02-01 10 10 # 2: 1 2018-02-20 20 30 # 3: 1 2018-03-12 30 50 # 4: 2 2018-02-01 10 10 # 5: 2 2018-02-12 20 30

**Solved: Running total until Today,** Running total upto and including Today in current month (and year) = VAR RunningTotal = CALCULATE(sum('Liquidit�'[Liquidit�]); FILTER(ALL('Calendar'); � rt Liquidità = CALCULATE(sum('Liquidità'[Liquidità]);FILTER((ALL('Calendar'[Data].[Date]));'Calendar'[Data].[Date] <= MAX('Calendar'[Data].[Date]))) but it gives value until the end of the period and if i fildar the date i gel the running total only for the selected dates. i need the running total value stops at the current mont and returns

**4 Ways to Calculate a Running Total With SQL,** over 1 year ago Calculating a running total/rolling sum in SQL is a useful skill to have. This might be useful to compare how much profit a driver brings in day -over-day per month. using this method typically forces the query to scan the table for every row that exists in the original table all over again. I think that there is an efficiency difference and ROWS UNBOUNDED PRECEDING AND CURRENT ROW is to be preferred (after testing of course and if it gives the results you want). Much more details you can find in the blog article by @Aaron Bertrand , including performance tests: Best approaches for running totals – updated for SQL Server 2012

**Solved: Cumulative Sum (MTD) Column for Month Using MySQL ,** I would like it to be grouped by day and by store, with a column for that I need a cumulative sum (MTD) for every store for every date. How would I make it so that the Sales MTD (cumulative) column is only summing for the current month? Before the statement you gave me, here is what I was trying:. Cumulative Total Sales := CALCULATE ( [Total Sales], FILTER ( ALL ( DimDate[Datekey] ), DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) ) )) Key parts of the Formula: The use of ALL(DimDate[DateKey]) results in the current context being ignored, so dates outside of the current pivot row context will be analyzed.

What I mean here is , the month selection will add up the values of previous months and current month here ..Cumulative sum for the current selection of month from the previous months. Any idea how can we do this in tableau. Regards, Anisur