Rolling average for last 3 calendar years

[LFM]

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?

[LFM]

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

    )

)

[rajendraongole1]

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]

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

[rajendraongole1]

you can try to create it in your date table. 

Eg: 

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

check this.

[LFM]

could you please share the file you made with sample data?

[rajendraongole1 · Accepted solution]

Hi @LFM  - please find the attached file.