Forum Discussion
Problem with Moving Average 12
Community ChampionAnonymous - One, I would personally avoid DATESINPERIOD. https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-DATEADD/m-p/1259467#M583
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
Finally, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi.
Thank you for your feedback.
I tried using the formulas, however I couldn't make it.
I'm using a lot of companies to analyze, my original chart has dates in rows and companies in columns, therefore I had to unpivot table to use it in BI properly, That's how my table looks like (unpivot)
| FECHA | Empresa | Price |
| 23/12/2009 | Acerias | $39.40 |
| 23/12/2009 | Isagen | $2,165.00 |
| 23/12/2009 | GrupoArgos | $18,980.00 |
| 24/12/2009 | Acerias | $41.65 |
| 24/12/2009 | Isagen | $2,200.00 |
| 24/12/2009 | GrupoArgos | $19,000.00 |
That's an example. I have more than 2000 rows with more companies (Data since 2009)
What I was using with the dateadd function was MovingAverage12 = AVERAGEX(DATESINPERIOD(Tecnicos[FECHA],MAX(Tecnicos[FECHA]),-16,DAY),[Total Precios])
The table I am receiving is the next one:
| FECHA | Price | MovingAverage12 |
| Friday, August 14, 2020 | 10,260.00 | 10,313.00 |
| Monday, August 17, 2020 | 10,260.00 | 10,315.00 |
| Tuesday, August 18, 2020 | 10,520.00 | 10,332.00 |
| Wednesday, August 19, 2020 | 10,660.00 | 10,357.00 |
| Thursday, August 20, 2020 | 10,740.00 | 10,402.00 |
| Friday, August 21, 2020 | 10,980.00 | 10,453.00 |
| Monday, August 24, 2020 | 11,320.00 | 10,549.00 |
| Tuesday, August 25, 2020 | 11,860.00 | 10,658.00 |
| Wednesday, August 26, 2020 | 11,820.00 | 10,785.00 |
| Thursday, August 27, 2020 | 11,900.00 | 10,920.00 |
The values I'm looking for, are the following.
| Friday, August 14, 2020 | 10,260.00 | 10,313.33 |
| Monday, August 17, 2020 | 10,260.00 | 10,305.00 |
| Tuesday, August 18, 2020 | 10,520.00 | 10,331.67 |
| Wednesday, August 19, 2020 | 10,660.00 | 10,356.67 |
| Thursday, August 20, 2020 | 10,740.00 | 10,401.67 |
| Friday, August 21, 2020 | 10,980.00 | 10,453.33 |
| Monday, August 24, 2020 | 11,320.00 | 10,533.33 |
| Tuesday, August 25, 2020 | 11,860.00 | 10,658.33 |
| Wednesday, August 26, 2020 | 11,820.00 | 10,785.00 |
| Thursday, August 27, 2020 | 11,900.00 | 10,920.00 |
As you may see, monday's averages are wrong because takes data from previous 16 days. In addition, Dates Column (Fecha) Just have dates from monday to friday, there are no weekends on that table as I'm not using it.
Thank you once again for your support. I'll appreciate your help once again.