Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
1 year ago
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   ...
Need Help
Tips and Tricks
  • wardy912's avatar
    wardy912
    1 year ago

    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

     

slorin's avatar
slorin
Super User
1 year ago

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