Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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)
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rh1b1ePIQvpqZlbVu?e=qdEXjW
1) 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.
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
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
pls try this
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.
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)
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.
always happy to help!
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
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?
Hi,
Share the download link of the MS Excel file.
Hi. It's here (but in .pdf file):
SINANWEB - Calendário Epidemiológico (saude.gov.br)
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.
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?
the evaluation context in DAX helps a lot sometimes, but, sometimes no
Thank you.
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |