Forum Discussion

Rickstor's avatar
Rickstor
Frequent Visitor
3 years ago
Solved

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 se...
Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    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's avatar
Rickstor
Frequent Visitor
3 years ago

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's avatar
Ahmedx
Icon for Super User rankSuper User
3 years ago

pls try this

  • Rickstor's avatar
    Rickstor
    Frequent Visitor
    3 years ago

    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's avatar
      Ahmedx
      Icon for Super User rankSuper User
      3 years ago

      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's avatar
        Rickstor
        Frequent Visitor
        3 years ago

        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.