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

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

      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

       

       

    • Ahmedx's avatar
      Ahmedx
      Icon for Super User rankSuper User
      3 years ago

      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'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