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
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.