SQL Server - Cumulative Sum over Last 12 Months, but starting from the Last Month (SQL Server 18)

Question

I need to run a cumulative sum of a value over the Last 12 Months. So far, my cumulative calculation are working, but starting from the Current Month. I need the total of Last 12 Months, starting from the Last Month. Currently, I'm using OVER clause on SQL, starting to running the cumulative total from the current row/month.

Please, refer below my code example:

SELECT  *,
         SUM(Amount) OVER (PARTITION BY ID ORDER BY Date_Month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS TwelveMoTtl
         FROM ( 
    SELECT DISTINCT
        CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, TransactionDt), 0) AS DATE)   AS Date_Month,
        ID,
        SUM(Amount) AS Amount
    FROM MyTable
    WHERE TransactionDt >= '2019-01-01'
    GROUP BY
        ID,
        CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, TransactionDt), 0) AS DATE)

Here is my results (using only one ID to simplify the example):

As my example, the calculation are starting from the current row, and running over the last 12 months.

If we take the February row for example, I need the cumulative sum from Jan, 2020 to February, 2019.

Any suggestions how could I do it?

Thanks,

Answer 1

You seem to understand window functions pretty well. You just have to adjust the window frame:

SUM(Amount) OVER (PARTITION BY ID
                  ORDER BY Date_Month
                  ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING
                 )