Rolling 12 Column Monthly Calculation

[jerryr125]

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
5/1/2024	32
6/1/2024	24
7/1/2024	16
8/1/2024	10
9/1/2024	15
10/1/2024	12
11/1/2024	32
12/1/2024	8	197
1/1/2025	12	201
2/1/2025	10	201
3/1/2025	15	202
4/1/2025	30	216
5/1/2025	12	196

Therefore, calculate the RollingTwelve column for each row as data is entered (dynamic).

Thanks - Jerry

[mromain]

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

[slorin]

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

[wardy912]

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

[jerryr125]

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

[ronrsnfld]

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"

[wardy912]

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