Forum Discussion

LFM's avatar
LFM
Helper III
1 year ago
Solved

Rolling average for last 3 calendar years

Hi. I have a table where I will use one column for date and another column for values. I want to use values to calculate the rolling average of the preceding three annual periods. It means the last e caluendar years, for 2022, 2023 and 2024. How can I make a nes column or measure calculating the rolling average?

Need Help
Show and Tell

6 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi LFM  - If you don't already have a calendar table, create one to ensure accurate date handling

    eg: CalendarTable = CALENDAR(MIN(YourTable[Date]), MAX(YourTable[Date]))

     

     

     

    and create a relationship with date table , date column.

     

    for rolling average for 3 yrs use the below calculation and replace the table name as per your model.

    Rolling Average 3 Years =
    VAR CurrentYear = YEAR(MAX(financials[Date]))
    VAR FilteredYears =
        FILTER(
            ALL(financials),
            YEAR(financials[Date]) <= CurrentYear &&
            YEAR(financials[Date]) > CurrentYear - 3
        )
    VAR RollingSum = SUMX(FilteredYears, financials[ Sales])
    VAR RollingCount = COUNTROWS(SUMMARIZE(FilteredYears,financials[Current Week]))
    RETURN
        DIVIDE(RollingSum, RollingCount)
    • LFM's avatar
      LFM
      Helper III
      1 year ago

      Thanks for reply I will try it out. I need to ask, where did you create the column current week?: financials[Current Week]

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        1 year ago

        you can try to create it in your date table. 

         

        Eg: 

        VAR RollingCount = COUNTROWS(SUMMARIZE(FilteredYears, YEAR(YourTable[Date])))

         

        check this.

  • LFM's avatar
    LFM
    Helper III
    1 year ago

    I made a column shown under that shows average for all values before a certain date, and for every date it shows average of all values before that date. I think I will do this for the last 3 calendar years only, but cannot find a solution 

    RollingAverageColumn =
    VAR CurrentDate = 'Total value'[Date]
    RETURN
    CALCULATE(
        AVERAGE('Total value'[Value]),
        FILTER(
            ALL('Total value'),
            'Total value'[Date] <= CurrentDate
        )
    )