Forum Discussion
Moving average measure with sum
Hello
I am new to this forum and calculated formulas in general - so bear with me if the question is 'too easy' :)
I have a simple data set like this example "FRUIT":
| DATE | ITEM | COUNT |
| 01-01-2017 | Apple | 10 |
| 01-01-2017 | Pear | 34 |
| 01-01-2017 | Banana | 20 |
| 02-01-2017 | Apple | 17 |
| 02-01-2017 | Pear | 5 |
| 02-01-2017 | Banana | 19 |
There a many more items and dates of course.
What I want to do, is make a measure that calculates the moving average of "COUNT", but disregarding the "ITEM". So a total moving average of the "DATE" only.
I found this helpful calculation for a measure to get the moving average, but it takes all the date rows into account, instead of the specific date in question .
AVERAGE 1 MONTH =
CALCULATE (
AVERAGEX ('FRUIT';FRUIT[COUNT]);
DATESINPERIOD (FRUIT[DATE];
LASTDATE (FRUIT[DATE]);
-1;MONTH))Is there any way to put in a summarize of the "DATE" column, and the do the moving average?
Best regards
David
8 Replies
Hi DavidB,
Based on your measure, you use DATESINPERIOD (FRUIT[DATE];LASTDATE (FRUIT[DATE]);-1;MONTH), which means it will calculate the the average during the past one month from your lastdate of FRUIT[DATE]. I am a little confusing about your calculation about specific, for example, you want to get (10+34+20)/3 for 2017-01-01, right? If it is, please use the formula to create a calculated column rather than measure.Average=CALCULATE(AVERAGE(FRUIT[COUNT]), ALLEXCEPT(FRUIT,FRUIT[DATE]))
Best Regards,
AngeliaThank you both for replying.
Yes v-huizhn-msft Angelica, you are correct about the assumption of what I want to get from the calculation.
But I also want the average of the last month (or 30 days) as a moving average.
I am trying to replicate the excel function in chart tools, where I can put in a "Two period moving average".
My data is too fluctuating to just make an average of the last few days - I need to follow it on a greater period.Maybe I am going about it all wrong, but I (and my boss) love that function in Excel, and I would like to know how to put it in my Power BI dashboard :)
Again, I am new to making formulas/code to get what I want.
Thank you.Hi DavidB,
For 2017-01-01, you don't want to get (10+34+20)/3, you need to the past month's(2016/12/1-2016/12/31) average? But there is no past month's data. Could you please create some fake sample table, and list the desired result, so that we can post solution which is close to your needs. Thanks for understanding.
Best Regards,
Aneglia
