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"
