cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fahid Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Yearly based rolling/moving average from 1877 to 2015

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

Super User
Super User

Re: Yearly based rolling/moving average from 1877 to 2015

@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

Super User
Super User

Re: Yearly based rolling/moving average from 1877 to 2015

@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 Regular Visitor
Regular Visitor

Rolling average per year based from 1877 to 2015

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

Super User
Super User

Re: Rolling average per year based from 1877 to 2015

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.

Fahid Regular Visitor
Regular Visitor

Re: Rolling average per year based from 1877 to 2015

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

Super User
Super User

Re: Yearly based rolling/moving average from 1877 to 2015

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

Super User
Super User

Re: Rolling average per year based from 1877 to 2015

Hi,

 

Share some data and show the expected result.

Highlighted
Stachu Super Contributor
Super Contributor

Re: Rolling average per year based from 1877 to 2015

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!

Proud to be a Datanaut!

Fahid Regular Visitor
Regular Visitor

Re: Yearly based rolling/moving average from 1877 to 2015

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

Re: Yearly based rolling/moving average from 1877 to 2015

@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

Super User
Super User

Re: Yearly based rolling/moving average from 1877 to 2015

@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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 44 members 846 guests
Please welcome our newest community members: