Rolling 12 Column Monthly Calculation

[jerryr125]

Hi

I am looking to do a Rolling 12 calculation in a table.

Example

Table_ABC:

Date	Quantity	RollingTwelve
1/1/2024	8
2/1/2024	10
3/1/2024	14
4/1/2024	16
5/1/2024	32
6/1/2024	24
7/1/2024	16
8/1/2024	10
9/1/2024	15
10/1/2024	12
11/1/2024	32
12/1/2024	8	197
1/1/2025	12	201
2/1/2025	10	201
3/1/2025	15	202
4/1/2025	30	216
5/1/2025	12	196

Therefore, calculate the RollingTwelve column for each row as data is entered (dynamic).

Thanks - Jerry

[SundarRaj]

Hi @jerryr125, here's another solution. Thanks

Here's the code:
let
Source = #table(
{"Date", "Quantity"},
{
{#date(2024, 1, 1), 8},
{#date(2024, 2, 1), 10},
{#date(2024, 3, 1), 14},
{#date(2024, 4, 1), 16},
{#date(2024, 5, 1), 32},
{#date(2024, 6, 1), 24},
{#date(2024, 7, 1), 16},
{#date(2024, 8, 1), 10},
{#date(2024, 9, 1), 15},
{#date(2024, 10, 1), 12},
{#date(2024, 11, 1), 32},
{#date(2024, 12, 1), 8},
{#date(2025, 1, 1), 12},
{#date(2025, 2, 1), 10},
{#date(2025, 3, 1), 15},
{#date(2025, 4, 1), 30},
{#date(2025, 5, 1), 12}
}
),
Custom1 = Table.TransformColumns(
Table.AddIndexColumn(Source, "12-Month Rolling", 0, 1),
{
"12-Month Rolling",
each List.Transform({_ - 11 .. _}, each try Source[Quantity]{_} otherwise null)
}
),
Custom2 = Table.TransformColumns(
Custom1,
{"12-Month Rolling", each if List.Count(List.RemoveNulls(_)) = 12 then List.Sum(_) else null}
)
in
Custom2

[mromain]

Hi,

Here another solution:

let
    Source = 
        let
            data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZFBDsIwDAT/knMkbCcp5ch+o/T/3wBUSBzFe+hlVE2sneNIz5ST6Ov2/Uyspryf+YfNY5XOy8Rr53XiW+fN82Kdb57b8NyJZyf3PCbe/lxl4v1d1fgetXiG8XvzGjdPI/M0f46b58OLBPN0P5YqlwckC5YsNjxBFpAsWLKU4Q+ygGQByQKSBSQL4iwgWUCygGTBksUkmOfynG8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StoreID = _t, Date = _t, Quantity = _t])
        in
            Table.TransformColumns(data,{{"Date", each Date.FromText(_, "en-US"), type date}, {"Quantity", Int64.From, Int64.Type}}),
    AddColumnRollingTwelve = 
        let 
            bSource = Table.Buffer(Source[[StoreID], [Date], [Quantity]]) 
        in 
            Table.AddColumn(Source, "RollingTwelve", each 
                let listRollingTwelve = Table.SelectRows(bSource, (r) => (r[StoreID] = [StoreID]) and (r[Date] > Date.AddMonths([Date], -12)) and (r[Date] <= [Date]))[Quantity] 
                in 
                    if List.Count(listRollingTwelve) = 12 then List.Sum(listRollingTwelve) else null
            , Int64.Type)
in
    AddColumnRollingTwelve

[slorin]

Hi @jerryr125 

Another possibility

let
Source = Your_Source,
Join = Table.NestedJoin(Source, {"StoreID"}, Source, {"StoreID"}, "Source", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Source", {"Date", "Quantity"}, {"Date.1", "Quantity.1"}),
Filter = Table.SelectRows(Expand, each [Date.1]>=Date.AddMonths([Date], -11) and [Date.1]<=[Date]),
Group = Table.Group(Filter, {"StoreID", "Date", "Quantity"}, 
   {{"Rolling Twelve", each if Table.RowCount(_)<12 then null else List.Sum([Quantity.1]), type number}})
in
Group

Stéphane

[wardy912 · Accepted solution]

You could add a calculated column:

RollingTwelve = 
CALCULATE(
    SUM(Table_ABC[Quantity]),
    FILTER(
        Table_ABC,
        Table_ABC[Date] <= EARLIER(Table_ABC[Date]) &&
        Table_ABC[Date] > EDATE(EARLIER(Table_ABC[Date]), -12)
    )
)

This will give you a rolling 12 month total for each row, results as follows

[jerryr125]

Thank you again for your help @wardy912 - appreciate it.

One more question - is there a way to do create this calculation within the actual table in the dataflow (Power BI workspace) - Power Query ?  Thanks - Jerry

[jerryr125]

Hi @wardy912 

Thank you so much for the reply.  Actually might do the trick.

How can I do a rolling 12 by StoreID ???

Table_ABC

Example:

StoreID	Date	Quantity	RollingTwelve
A	1/1/2024	8
A	2/1/2024	10
A	3/1/2024	14
A	4/1/2024	16
A	5/1/2024	32
A	6/1/2024	24
A	7/1/2024	16
A	8/1/2024	10
A	9/1/2024	15
A	10/1/2024	12
A	11/1/2024	32
A	12/1/2024	8	197
A	1/1/2025	12	201
A	2/1/2025	10	201
A	3/1/2025	15	202
A	4/1/2025	30	216
A	5/1/2025	12	196
B	1/1/2024	15
B	2/1/2024	10
B	3/1/2024	22
B	4/1/2024	16
B	5/1/2024	32
B	6/1/2024	30
B	7/1/2024	16
B	8/1/2024	10
B	9/1/2024	15
B	10/1/2024	12
B	11/1/2024	32
B	12/1/2024	8	218
B	1/1/2025	12	215
B	2/1/2025	10	215
B	3/1/2025	15	208
B	4/1/2025	20	212
B	5/1/2025	10	190

[ronrsnfld]

In Power Query M-Code, you can do this by embedding the rolling sum algorithm within a Table.Group function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLDoMwDEXRvWSMhO0kQIf1NhD73waVCv4kb4au0MOc8yzfshReeRWS9ns8yrX8o3hkslpDbVZbqJvV7rWK1c2r+MIOFw54wyfUbpUpZP8cM7yCBf3z827PExLqJNHzFc1rpVEi7GpmfxYUumt2F1+Y3RW6a3avvju7K3RX6K7YXbG7QvcgMfjM7kGi5yuCu9Ao8S5cNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StoreID = _t, Date = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StoreID", type text},{"Date", type date}, {"Quantity", Int64.Type}}),

    #"Group by Store" = Table.Group(#"Changed Type","StoreID",{

    {"Add 12 Month Sum", (t)=> 
        [a=List.Accumulate(
            List.Numbers(0, Table.RowCount(t)),
            {},
            (s,c)=> s & {if c < 11 then null else List.Sum(List.Range(t[Quantity],c-11,12))}),
        
         b=Table.FromColumns(
             Table.ToColumns(t)
             & {a}, {"StoreID", "Date", "Quantity", "Rolling Twelve"})][b],
                type table[StoreID=text, Date=date, Quantity=Int64.Type, Rolling Twelve=Int64.Type]}}),
    
    #"Expanded Add 12 Month Sum" = Table.ExpandTableColumn(#"Group by Store", "Add 12 Month Sum", {"Date", "Quantity", "Rolling Twelve"})
in
    #"Expanded Add 12 Month Sum"

[wardy912]

RollingTwelve = 
CALCULATE(
    SUM('Table_ABC'[Quantity]),
    FILTER(
        'Table_ABC',
        'Table_ABC'[StoreID] = EARLIER('Table_ABC'[StoreID]) &&
        'Table_ABC'[Date] <= EARLIER('Table_ABC'[Date]) &&
        'Table_ABC'[Date] > EDATE(EARLIER('Table_ABC'[Date]), -12)
    )
)

Please give a thumbs up and confirm the solution if this helps, thanks

[wardy912]

Hi @jerryr125 

 I don't think you can achieve this easily, you would need to duplicate the table and reference the 2. I have an AI generated answer for you, but I haven't checked if it works as it doesn't look like a great solution. Feel free to try it and please mark as solved and give a thumbs up if this or my previous answers help.

To replicate the logic of your DAX CALCULATE expression in Power Query (M language), you'll need to follow a different approach since Power Query doesn't support row context or functions like EARLIER. Instead, you can achieve the same result by:

1. Creating a self-join on StoreID and Date with the appropriate filtering.
2. Aggregating the Quantity values from the filtered rows.
3. Merging the result back into the original table as a custom column.

Here's how you can do it step-by-step in Power Query:

---

🛠 Step-by-Step in Power Query

Assume your table is called Table_ABC.

1. Duplicate the table

• Right-click Table_ABC in the Queries pane → Duplicate.
• Rename the duplicate to Table_ABC_Join.

2. Merge the tables

• Go to the original Table_ABC.
• Use Home > Merge Queries.
• Merge Table_ABC with Table_ABC_Join using:
  • StoreID from both tables (equals)
  • Date from Table_ABC_Join is less than or equal to Date from Table_ABC
  • Date from Table_ABC_Join is greater than Date minus 12 months from Table_ABC

Since Power Query doesn’t support complex merge conditions directly, you’ll need to:

3. Add helper columns

In both tables:

• Add a column DateMinus12Months

= Date.AddMonths([Date], -12)
​

Then in Table_ABC, merge with Table_ABC_Join using:

• StoreID = StoreID
• Date >= Date in Table_ABC_Join
• DateMinus12Months < Date in Table_ABC_Join

This requires merging on multiple columns and filtering after the merge.

4. Expand and filter

• After merging, expand the Quantity column from the joined table.
• Filter the rows to keep only those where

[Table_ABC_Join.Date] <= [Date] and [Table_ABC_Join.Date] > [DateMinus12Months]
​

5. Group and sum

• Group the expanded rows by the original row’s identifier (e.g., an Index column or combination of StoreID and Date).
• Sum the Quantity column.

6. Merge the result back

• Merge the grouped result back into the original Table_ABC to get the cumulative quantity as a new custom column.