Moving Average 5 periods

[Rickstor]

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. 

[Ahmedx]

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rh1b1ePIQvpqZlbVu?e=qdEXjW

1) You need to create two calculated columns

2) write measures

[Rickstor]

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]

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: 

-----

write like thisА:

WINDOW(-4,REL,0,REL

[Rickstor]

this works: 

WINDOW(-4,REL,0,REL

But, when I add the column 'cod_region' doesn't work (the moving average values is wrong). Even adding the column 'cod_region' to the measure '5 step', didn't work. What I'm doing wrong? 

I attached your .pbix file with the data. Please, could you help me again?
Link: pbix moving average

[Ahmedx]

pls try this

[Rickstor]

Hi @Ahmedx , almost done! Works great whitout 'code_region'. When I add this column, the values are the same for all regions. Like this:

using only 'year' it's great, it's working!
I tried change the ALL() to ALLSELECTED(), but doesn't work. 

What's missing?

Thank you for your help. 

[Ahmedx · Accepted solution]

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)

[Rickstor]

Hi. Your solution was amazing. Thank you. You helped me a lot. I apologize for the lack of response. I've been dealing with several health issues over the past few months, but I'm back now.

[Ahmedx]

always happy to help!

[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

[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.

[Rickstor]

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?

[Ashish_Mathur]

Hi,

Share the download link of the MS Excel file.

[Rickstor]

Hi. It's here (but in .pdf file):
SINANWEB - Calendário Epidemiológico (saude.gov.br)

[Rickstor]

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]

what doesn't work, write how it doesn't work

[Rickstor]

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? 

[Rickstor]

the evaluation context in DAX helps a lot sometimes, but, sometimes no

[Ahmedx]

pls try this

Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26Rh1QdLQru4D5aoxXC?e=FUL9ap

[Rickstor]

Thank you.