Forum Discussion
Rolling 12 Column Monthly Calculation
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
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 |
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
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:
- Creating a self-join on StoreID and Date with the appropriate filtering.
- Aggregating the Quantity values from the filtered rows.
- 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.
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"
