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

     

wardy912's avatar
wardy912
Super User
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

 

jerryr125's avatar
jerryr125
Helper IV
1 year ago

Thank you again for your help wardy912 - appreciate it.

One more question - is there a way to do create this calculation within the actual table in the dataflow (Power BI workspace) - Power Query ?  Thanks - Jerry