Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
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] ) ) )
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,
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.
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
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)
Hi,
Share some data and show the expected result.
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
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 |
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] ) ) )
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
95 | |
92 | |
86 | |
69 |
User | Count |
---|---|
162 | |
129 | |
128 | |
104 | |
98 |