Yearly based rolling/moving average from 1877 to 2015

[Fahid]

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

[AlB · Accepted solution]

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 

[AlB · Accepted solution]

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

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

[Ashish_Mathur]

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]

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]

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)

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[AlB · Accepted solution]

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 

[Fahid]

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

Year	Speed
1987	299.9232
1987	540.0002
1988	540.0002
1988	500
1989	411.48
1989	640.08
1990	182.88
1991	499.872
1992	304.8
1993	304.8
1994	800.0001
1995	199.9488
1996	426.72
1997	792.48
1998	792.48
1999	792.48
2000	299.9232
2001	299.9232
2002	600.1512
2003	600.1512
2004	731.52
2005	731.52
2006	731.52
2007	762
2008	762
2009	600.1512
2010	399.8976
2011	1680
2012	1700
2013	1700
2014	249.936
2015	1299.999

[AlB · Accepted solution]

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

[AlB · Accepted solution]

@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.

[Fahid]

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