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.