Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
LFM
Helper III
Helper III

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?

1 ACCEPTED SOLUTION

Hi @LFM  - please find the attached file.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

6 REPLIES 6
LFM
Helper III
Helper III

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
Super User
Super User

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]))

 

rajendraongole1_0-1737108825892.png

 

 

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)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

you can try to create it in your date table. 

 

Eg: 

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

 

check this.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

Hi @LFM  - please find the attached file.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.