Forum Discussion
Moving Average 5 periods
Hello. Please help me.
I need a moving average, 5 periods, in Power BI, using a measure.
This is so easy in Microsoft Excel. I need this:
The column 'year' has a text format.
After search in a lot of messages here, I couldn't find something that apply to my problem.
Thank you.
pls try this
5 step 3 = VAR _CurentRx = MAX('data'[Rx]) VAR _Results = CALCULATE(SUM(data[value]), FILTER(ALL(data),'data'[Rx]<=_CurentRx&&'data'[Rx]>=_CurentRx-4), VALUES(data[cod_region])) /5 RETURN IF(_CurentRx< 6 , BLANK(),_Results)
21 Replies
- Ahmedx
Super User
the evaluation context in DAX helps a lot sometimes, but, sometimes no
- Ahmedx
what doesn't work, write how it doesn't work
your solution almost work. Look:
In a excel I have this:
The moving average just go on through the years.
Using your solution in DAX I have this (I think that is because effect of evaluation context):
Sorry. Was my fault. In my original post I didn't show the other years.
How to fix?
- Ashish_Mathur
Hi,
What do the numbers in the SE column represent? Are they week numbers? If yes, then if you have a Calendar Table with week numbers as well, there is a fairly straight forward solution.
Yes, they are week numbers. Like this:
It's a epidemiological calendar.
Maybe can I use the column "End", using relationship between column 'SE' and my other dataset?my previous post, that is a problem, I don't have the epidemiological calendar from years 2022 and 2021. In my project, I only need 2023 calendar.
- Ahmedx
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rh1b1ePIQvpqZlbVu?e=qdEXjW1) You need to create two calculated columns
2) write measures
Amazing! Hi Ahmedx , your solution works. How can I change de measure to use the current 'SE' in measure? Example: 'SE' = 6, the moving average will be (36+37+24+41+42) / 5. Like this image:
Your measure works great but I add another column (I have a column with region codes, using a relationship with a table dimension of region codes) and the measure don't work. Like this:
The dimension table is like this:
How can I change the measure to work's with the region codes?
Thank you.
- Ahmedx
you need to add this column to the virtual table, like this:
step = if( MAX('Table'[Rx])<6 , BLANK(), CALCULATE(SUM('Table'[Value]) , WINDOW(-5,REL,-1,REL,SUMMARIZE(ALLSELECTED('Table'),'Table'[year],'Table'[SE],'Table'[Rx],'Table'[Region]),ORDERBY('Table'[Rx],ASC)) )/5)+0
