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

     

SundarRaj's avatar
SundarRaj
Super User
1 year ago

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