Forum Discussion
Solved
Rolling 12 Column Monthly Calculation
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 ...
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
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 |
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"