Get sum of entries over last 6 months (incomplete months)

how to calculate rolling 12 months
rolling 12 months excel pivot table
dax last 3 months
pivot table last 6 months
dax last 6 months
last 6 months formula
3 month moving average in pivot table
last n months

My data looks something like this

ProductNumber | YearMonth | Number
1               201803      1
1               201804      3
1               201810      6
2               201807      -3
2               201809      5

Now what I want to have is add an additional entry "6MSum" which is the sum of the last 6 months per ProductNumber (not the last 6 entries). Please be aware the YearMonth data is not complete, for every ProductNumber there are gaps in between so I cant just use the last 6 entries for the sum. The final result should look something like this.

ProductNumber | YearMonth | Number | 6MSum
1               201803      1        1
1               201804      3        4
1               201810      6        9
2               201807      -3       -3
2               201809      5        2

Additionally I don't want to insert the sum to the table but instead use it in a query like:

SELECT [ProductNumber],[YearMonth],[Number],
FROM ...

I found a lot off solutions that use a "sum over period" but only for the last X entries and not for the actual conditional statement of "YearMonth within last 6 months".

Any help would be much appreciated!

Its a SQL Database


It seems to be the case that the gaps within the months have to be filled with data, afterwards something like

sum(Number) OVER (PARTITION BY category 
                      ORDER BY year, week 
                          ROWS 6 PRECEDING) AS 6MSum

Should work.

Reference to the solution :

Just to provide one more option. You can use DATEFROMPARTS to build valid dates from the YearMonth value and then search for values within date ranges.

Testable here:

    ProductNumber , YearMonth , Number  
FROM ( values 
    (1,  201803 ,     1  ),
    (1,  201804 ,     3  ),
    (1,  201810 ,     6  ),
    (2,  201807 ,     -3 ),
    (2,  201809 ,     5  )
) s (ProductNumber , YearMonth , Number)

,[6MSum] = (SELECT SUM(number) FROM #t WHERE
    ProductNumber = t.ProductNumber 
    AND DATEFROMPARTS(LEFT(YearMonth,4),RIGHT(YearMonth,2),1) --Build a valid start of month date
        DATEADD(MONTH,-6,DATEFROMPARTS(LEFT(t.YearMonth,4),RIGHT(t.YearMonth,2),1)) --Build a valid start of month date 6 months back
        AND DATEFROMPARTS(LEFT(t.YearMonth,4),RIGHT(t.YearMonth,2),1)) --Build a valid end of month date
FROM #t t


Circular, Line 7 (Applications pending (action incomplete) end of this month). the last day of the month, as described in Section C. The entries on line 7 should equal The entries on line 8 should be equal to the sum of the entries on lines 9, 13, 14 and 15 and should be the same as the entries on lines 6 of the same column. b​. This formula uses the named ranges "amount" (C4:C9) and "date" (B4:B9).. How this formula works. The SUMIFS function can sum values in ranges based on multiple criteria. In this case, we configure SUMIFS to sum amounts by month using two criteria: one to match dates greater than or equal to the first day of the month, one to match dates less than or equal to the last day of the month.

You could go the OUTER APPLY route. The following produces your required results exactly:

-- prep data 
    ProductNumber , YearMonth , Number  
into #t
FROM ( values 
    (1,  201803 ,     1  ),
    (1,  201804 ,     3  ),
    (1,  201810 ,     6  ),
    (2,  201807 ,     -3 ),
    (2,  201809 ,     5  )
) s (ProductNumber , YearMonth , Number)

-- output
FROM #t t
outer apply ( 
        sum(number) as [6MSum]
    FROM #t it
        it.ProductNumber = t.ProductNumber 
    and it.yearmonth <= t.yearmonth 
    and t.yearmonth - it.yearmonth between 0 and 6    
) tt

drop table #t

FHA Techniques of Housing Market Analysis, Enter from available records the number of units in multifamily structures not multifamily projects authorized in the past six months are incomplete ( longer for high in the number of vacant units ( plus or minus ) by subtracting Column D ( 6 ) The rate is derived from a formula designed to calculate the rate of change on a  Hi, Can you please help me to get the formula for the sum of last 3 and 6 values in a row. Attaching the sample workbook for your reference Thanks in advance 323458 Sum of Last 3 and 6 months [SOLVED]

Use outer apply and convert yearmonth to a date, something like this:

with t as (
      select t.*,
             convert(date, convert(varchar(255), yearmonth) + '01')) as ymd
      from yourtable t
select t.*, t2.sum_6m
from t outer apply
     (select sum(t2.number) as sum_6m
      from t t2
      where t2.productnumber = t.productnumber and
            t2.ymd <= t.ymd and
            t2.ymd > dateadd(month, -6, ymd)
     ) t2;

Elihu Root Collection of United States Documents Relating to the , retained by the company commander as part of the company records. add P3 for each complete month of service since date of last enlistment to March 31, of service of any incomplete month may be a part of thirty days;1 add to the sum so (6) Each company commander will prepare a statement of these balances due​  create table tbl1 ( year number, month varchar2(3), col1 varchar2(10), col2 varchar2 (20), amount number) I need to list the values of col1, col2 along with the value as the below calculation (I believe it is rolling sum) { SUM(amount) for year , month (Ex: 2016, 02) , col1, col2} / {sum (amount) for past 12 months Mar 15 - Feb 16}.

So a working query (provided by a colleauge of mine) can look like this

SELECT [YearMonth]
 , (Select Sum(Number) from [...] DPDS_1 where DPDS.ProductNumber = 
DPDS_1.ProductNumber and DPDS_1.YearMonth <= DPDS.YearMonth and DPDS_1.YearMonth >= 
convert (int, left (convert (varchar, dateadd(mm, -6, DPDS.YearMonth + '01'), 112), 
6)))FROM [...] DPDS

JCT98 Building Contract: Law and Administration, The JCT 98 therefore allows for the adjustment of the Contract Sum in defined situations. Not later than 6 months after Practical Completion, the Contractor is to send to If final accounts are produced on incomplete information, the Contractor 'Period of Interim Certificates', the length of which is an entry in the Appendix. From what I understand this won't summarize over all three months, it will just summarize what happened three months ago. I.e. if I put "Safety Briefings last 3 months" in a table grouped per month and look at the April-row, then I will see the sum of January and not the sum of Januari-March, right!?

Solved: Calculating a 6 month running sum, In my EG project, I have Dates, Values, and running CUM values. I need another column for the cumulative value for the past 3 months. where the condition is that the date is between the date and date-3 so you get the two previous values. previous months sum (to ignore an incomplete current month). I want to calculate the average for L3 and L6, dynamically, which means with every new month sales value added in the table, BI automatically adjusts the period to calculate the average for last 3 months and 6 months. E.g. In Jan, it should calculate L3 as average{nov,dec,jan} and L6 as {aug,sep,oct

Journal of the National Cancer Institute, You can use a windowed function like sum over partition . SELECT [​ProductNumber],[YearMonth],[Number], sum(Number) over (partition by ProductNumber) as  The query you have. You could simplify your query using a WINDOW clause, but that's just shortening the syntax, not changing the query plan.. SELECT id, trans_ref_no, amount, trans_date, entity_id , SUM(amount) OVER w AS trans_total , COUNT(*) OVER w AS trans_count FROM transactiondb WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY trans_date ROWS BETWEEN UNBOUNDED

Dynamically Show Completed Months in Power BI, Cost of care for an individual cancer case patient. a = prediagnostic period; b = initial to data entry clerks if information was incomplete, and monthly comparisons of the (32), we summarized costs for 6 months of initial and terminal this sum by the total number of personquarters of each case subject care in the phase. Hello, I want to calculate the following: the average for the last 3 months and i want to project this data for the rest of the year. For example: the data for the first 3 months is real data jan feb mar apr may jun jul 10 20 30 20 20 20 20 I

  • Which dbms are you using?
  • If your data has gaps and youre not using a database that understands RANGE BETWEEN (i.e. youre not using oracle) you'll have to pad your data out with entries for the missing months. This isn't hard - just generate a series of negative numbers and ADD_MONTHS them to some date, then left join your data onto this contiguous set. After you do this "the last 6 rows" will be equivalnt to "the last 6 months" because there are no gaps
  • @Skiwan this question will likely remain unanswered until you tell us what database you're using
  • Simply subtracting 201712 and 201801 won't help.
  • how exactly does the between 0 and 6 work here in this instance?
  • @SalmanA is correct - you should really convert to Dates and then do a DateDiff.
  • @Skiwan - the Between 0 and 6 just checks that the dates are within 6 months
  • Try converting the yearmonth e.g. 201803 to a date using datefromparts('2018', '03', 1)... then use datediff to check if they are 6 months apart or less.