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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fahid
Helper I
Helper I

Yearly based rolling/moving average from 1877 to 2015

Hello everyone,

 

I need help badly. I want to find out rolling average per year based on my entire data from 1877 to 2015. I must do it yearly based as my data doesn’t have month information.

 

 

 

Can someone please help me to find a way to calculate yearly based rolling average from 1877 to 2015? Thanks in advance.

 

BR,

Fahid

3 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @Fahid

 

First of all, you need to provide more info if you want people to be able to answer. You'd have to show at least the structure of the tables in your dataset, and an example based on your sample data showing what you need and the fields involved. Otherwise you'll be discouraging potential answerers.

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

 

Now, I believe DAX does not support dates before 1900 in date format or through time intelligence functions. Since you say you only have info on the year, that should not matter much. I guess you have a column Year so you could try something like the following, where Table1 is the name of your table:

 

1. Place Table1[Year] in the rows of a matrix visual

2. Create this measure and place it in values of the matrix, where Table1[ColumnToAverage] is the column on which you need the rolling average:     

 

RollingAvg =
CALCULATE (
    AVERAGE ( Table1[ColumnToAverage] ),
    FILTER ( ALL ( Table1[Year] ), Table1[Year] <= MAX ( Table1[Year] ) )
)

 

Again, my answer cannot be accurate enough because the info provided is not enough 

 

 

View solution in original post

@Fahid

 

From what I understand, the code that I provided earlier should already do the job. It calculates the average from the beginning until the current year. I see that there are years with more than one entry in your sample data, like 1987. How do you want to deal with that case? The current code just treats them as different years. If this is not what you need you should explain in detail what it is that you need. That's why I recommended to use an example based in your sample data. It's usually the best way to get your message across.

Like I said, the procedure that I suggested in my previous post should be valid, so I repeat it here just updating the names of the columns to match those in your sample data:

 

1. Place Table1[Year] in the rows of a matrix visual

2. Create this measure and place it in values of the matrix:

 

RollingAvg =
CALCULATE (
    AVERAGE ( Table1[Speed] ),
    FILTER ( ALL ( Table1[Year] ), Table1[Year] <= MAX ( Table1[Year] ) )
)

You can also use this measure, which will yield the same result 

 

RollingAvg_v2 =
CALCULATE (
    AVERAGE ( Table1[Speed] ),
    FILTER ( ALL ( Table1[Year] ), Table1[Year] <= SELECTEDVALUE ( Table1[Year] ) )
)

 

View solution in original post

@Fahid

 

Since you talk about a moving average, there's the option of calculating the average over a moving window, like for example over the last 20 years (or any other amount). Again, if you want something like this it is not difficult to do but you need to explain it. The current code just looks at all the years previous to the current, since the beginning of your data.

 

Here you have a file in which you can see the setup and measures described before based on your sample data.

View solution in original post

10 REPLIES 10
Fahid
Helper I
Helper I

Hello,

I want to find out rolling average per year based on my entire data from 1877 to 2015. I must do it yearly based as my data doesn’t have month information.

I found below Rolling 12 Month Average calculation from this forum, but I don’t know how to change it to yearly format. 

 

[Rolling 12 Month Average] = DIVIDE(
CALCULATE(
SUM(Avg_sales[Sales_Count]),
DATESBETWEEN(
Avg_sales[List_Date],
FIRSTDATE(DATEADD(Avg_sales[List_Date],-11,MONTH)),
LASTDATE('Avg_sales'[List_Date])
)
),12)

 

Can someone please help me to covert it yearly based from 1877 to 2015? Thanks in advance.

 

BR,

Fahid

Hi,

 

See if this helps.  Change -11 to -1 and Month to Year.  Furthermore, i think PowerBI desktop does not recognise dates prior to 1/1/1900.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur,

 

Thanks for your reply but this solution is not working correctly. Can you please suggest me any other way to calculate yearly based rolling average?

 

BR,

Fahid

Stachu
Community Champion
Community Champion

try this measure, you can specify the number of rolling years to be included in the average

AvgLastXYears = 
VAR __NrOfYears = 5
VAR __CurrentYear = MAX('Table'[Year])
VAR __RelevantYears = FILTER(ALL('Table'), ('Table'[Year]<=__CurrentYear) && ('Table'[Year]>__CurrentYear-__NrOfYears))
RETURN
DIVIDE(CALCULATE(SUM('Table'[Value]), __RelevantYears),__NrOfYears)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlB
Super User
Super User

Hi @Fahid

 

First of all, you need to provide more info if you want people to be able to answer. You'd have to show at least the structure of the tables in your dataset, and an example based on your sample data showing what you need and the fields involved. Otherwise you'll be discouraging potential answerers.

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

 

Now, I believe DAX does not support dates before 1900 in date format or through time intelligence functions. Since you say you only have info on the year, that should not matter much. I guess you have a column Year so you could try something like the following, where Table1 is the name of your table:

 

1. Place Table1[Year] in the rows of a matrix visual

2. Create this measure and place it in values of the matrix, where Table1[ColumnToAverage] is the column on which you need the rolling average:     

 

RollingAvg =
CALCULATE (
    AVERAGE ( Table1[ColumnToAverage] ),
    FILTER ( ALL ( Table1[Year] ), Table1[Year] <= MAX ( Table1[Year] ) )
)

 

Again, my answer cannot be accurate enough because the info provided is not enough 

 

 

Hello @AlB,

 

Thanks for your tips. I have a too long dataset and have NDA and that is why can’t copy paste here. Demo data looks like below table. I am trying to calculate rolling average of Speed per Year based which preferably should also work automatically when new data gets added. Could you please help me by providing a solution?

 

BR,

Fahid

 

YearSpeed
1987299.9232
1987540.0002
1988540.0002
1988500
1989411.48
1989640.08
1990182.88
1991499.872
1992304.8
1993304.8
1994800.0001
1995199.9488
1996426.72
1997792.48
1998792.48
1999792.48
2000299.9232
2001299.9232
2002600.1512
2003600.1512
2004731.52
2005731.52
2006731.52
2007762
2008762
2009600.1512
2010399.8976
20111680
20121700
20131700
2014249.936
20151299.999

@Fahid

 

From what I understand, the code that I provided earlier should already do the job. It calculates the average from the beginning until the current year. I see that there are years with more than one entry in your sample data, like 1987. How do you want to deal with that case? The current code just treats them as different years. If this is not what you need you should explain in detail what it is that you need. That's why I recommended to use an example based in your sample data. It's usually the best way to get your message across.

Like I said, the procedure that I suggested in my previous post should be valid, so I repeat it here just updating the names of the columns to match those in your sample data:

 

1. Place Table1[Year] in the rows of a matrix visual

2. Create this measure and place it in values of the matrix:

 

RollingAvg =
CALCULATE (
    AVERAGE ( Table1[Speed] ),
    FILTER ( ALL ( Table1[Year] ), Table1[Year] <= MAX ( Table1[Year] ) )
)

You can also use this measure, which will yield the same result 

 

RollingAvg_v2 =
CALCULATE (
    AVERAGE ( Table1[Speed] ),
    FILTER ( ALL ( Table1[Year] ), Table1[Year] <= SELECTEDVALUE ( Table1[Year] ) )
)

 

@Fahid

 

Since you talk about a moving average, there's the option of calculating the average over a moving window, like for example over the last 20 years (or any other amount). Again, if you want something like this it is not difficult to do but you need to explain it. The current code just looks at all the years previous to the current, since the beginning of your data.

 

Here you have a file in which you can see the setup and measures described before based on your sample data.

Hello @AlB,

 

Thank you so much for explaining so clearly. Your both codes are working and giving my expected result. Though my main data is too old & has some other issues and not working properly. I will try to fix it by myself. I am marking all your answers as solutions. Thanks again for giving me time.

 

BR,

Fahid

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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