The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi,
I need to calculate simple moving average for certain dates that do not come into succession, just an average of previous 3 figures, then for next figures, etc.. With date formatted column DAX inserts all the missing days and calculations get messed up.
Here is an example from Excel.
Can you please help me replicate this result in DAX?
Thank you!
Date Sales 3PeriodMovingAverage
06/21/18 | 16 | |
06/24/18 | 9 | |
06/26/18 | 10 | 11.67 |
07/01/18 | 9 | 9.33 |
07/02/18 | 7 | 8.67 |
07/05/18 | 13 | 9.67 |
07/08/18 | 17 | 12.33 |
07/12/18 | 11 | 13.67 |
07/13/18 | 9 | 12.33 |
07/18/18 | 11 | 10.33 |
07/20/18 | 13 | 11.00 |
07/21/18 | 12 | 12.00 |
07/26/18 | 8 | 11.00 |
07/29/18 | 18 | 12.67 |
08/02/18 | 19 | 15.00 |
08/03/18 | 15 | 17.33 |
08/04/18 | 14 | 16.00 |
08/08/18 | 17 | 15.33 |
08/10/18 | 11 | 14.00 |
08/15/18 | 19 | 15.67 |
08/19/18 | 17 | 15.67 |
08/21/18 | 14 | 16.67 |
Solved! Go to Solution.
HI @Anonymous
One approach is to add an Index column and then use that to generate your "last three items" average. I have attached a PBIX file (you can hide the Index col if you like).
The index calculated column can be added like this
Index = RANKX('Table1','Table1'[Date],,ASC)
With the moving average column using it in this way
Moving Average = VAR MyIndex = Table1[Index] VAR myResult = AVERAGEX( FILTER( 'Table1', 'Table1'[Index] > MyIndex-3 && 'Table1'[Index] <= MyIndex ),'Table1'[Value] ) RETURN FIXED(myResult,2)
HI @Anonymous
One approach is to add an Index column and then use that to generate your "last three items" average. I have attached a PBIX file (you can hide the Index col if you like).
The index calculated column can be added like this
Index = RANKX('Table1','Table1'[Date],,ASC)
With the moving average column using it in this way
Moving Average = VAR MyIndex = Table1[Index] VAR myResult = AVERAGEX( FILTER( 'Table1', 'Table1'[Index] > MyIndex-3 && 'Table1'[Index] <= MyIndex ),'Table1'[Value] ) RETURN FIXED(myResult,2)
Thanks for the suggestion! How do I resolve the same question, using moving standart deviation? I tried to substitute AVERAGEX for STDEVX.P, but the result was not correct.
I believe I've found my mistake. I had to fill the blank values with 0 and use STDEVX.S
Hi!
Very useful post but I have another question. How can I calculate moving average without creating calculated columns and using only measures? I'm connected to SQL analysis server and I cannot create calculated columns.
I'd like to calculate average duration of the last 3 Name_IDs. My goal is to plot Name_IDs on x axis and Moving average as value.
Input:
My current attempt:
Moving average =
VAR index = RANKX (
ALLSELECTED ( NAME ),
CALCULATE(MAX(Table1[EndDate])),
,
ASC
)
Return
AVERAGEX(
FILTER(
Table1,
indexName >index-3 && -- indexName is another measure that is equal to VAR index
indexName <= index
),Table1[Duration]
)
Thank you!
@Phil_Seamark you, sir, are a HERO.
I was trying to create moving averages using dates, but every time I filtered the date to show only the last 12 months (and I have 5 years of data, so it needs filtering) the measure would crash. Using an index solved the problem, I would never think of this.
Thanks for sharing.
Hello @Phil_Seamark once again 🙂
I am still stuck with the task. I need a average for prices that are collected on a weekly basis, but often the days are omitted, for that I have created an index that reset after change. It has 4 levels - Region, Municipality, Product Category, Product Name - after we move to next municipality with different category and product, index is reset.
When I use your formula on this file, I get huge numbers, I think it is summing everything beneath without taking into consideration index.
Thanks in advance!
Best,
Levan
Phil, thank you so much!
User | Count |
---|---|
160 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |