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

     

mromain's avatar
mromain
Regular Visitor
1 year ago

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