Rolling 12 Column Monthly Calculation

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

Hi,

Here another solution:

let
    Source = 
        let
            data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZFBDsIwDAT/knMkbCcp5ch+o/T/3wBUSBzFe+hlVE2sneNIz5ST6Ov2/Uyspryf+YfNY5XOy8Rr53XiW+fN82Kdb57b8NyJZyf3PCbe/lxl4v1d1fgetXiG8XvzGjdPI/M0f46b58OLBPN0P5YqlwckC5YsNjxBFpAsWLKU4Q+ygGQByQKSBSQL4iwgWUCygGTBksUkmOfynG8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StoreID = _t, Date = _t, Quantity = _t])
        in
            Table.TransformColumns(data,{{"Date", each Date.FromText(_, "en-US"), type date}, {"Quantity", Int64.From, Int64.Type}}),
    AddColumnRollingTwelve = 
        let 
            bSource = Table.Buffer(Source[[StoreID], [Date], [Quantity]]) 
        in 
            Table.AddColumn(Source, "RollingTwelve", each 
                let listRollingTwelve = Table.SelectRows(bSource, (r) => (r[StoreID] = [StoreID]) and (r[Date] > Date.AddMonths([Date], -12)) and (r[Date] <= [Date]))[Quantity] 
                in 
                    if List.Count(listRollingTwelve) = 12 then List.Sum(listRollingTwelve) else null
            , Int64.Type)
in
    AddColumnRollingTwelve

Hi jerryr125, here's another solution. Thanks

Here's the code:
let
Source = #table(
{"Date", "Quantity"},
{
{#date(2024, 1, 1), 8},
{#date(2024, 2, 1), 10},
{#date(2024, 3, 1), 14},
{#date(2024, 4, 1), 16},
{#date(2024, 5, 1), 32},
{#date(2024, 6, 1), 24},
{#date(2024, 7, 1), 16},
{#date(2024, 8, 1), 10},
{#date(2024, 9, 1), 15},
{#date(2024, 10, 1), 12},
{#date(2024, 11, 1), 32},
{#date(2024, 12, 1), 8},
{#date(2025, 1, 1), 12},
{#date(2025, 2, 1), 10},
{#date(2025, 3, 1), 15},
{#date(2025, 4, 1), 30},
{#date(2025, 5, 1), 12}
}
),
Custom1 = Table.TransformColumns(
Table.AddIndexColumn(Source, "12-Month Rolling", 0, 1),
{
"12-Month Rolling",
each List.Transform({_ - 11 .. _}, each try Source[Quantity]{_} otherwise null)
}
),
Custom2 = Table.TransformColumns(
Custom1,
{"12-Month Rolling", each if List.Count(List.RemoveNulls(_)) = 12 then List.Sum(_) else null}
)
in
Custom2