Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Moving Average by Ranked Index

Dear Community,    I am trying to calculate the 13 Week Moving Average.   My data model is very simple.    Please see below for my current attempt and error message.              G...
Need Help
  • BrianConnelly's avatar
    BrianConnelly
    3 years ago

    Here is your code...

     

     

    90 Day Average = 
VAR sDate = MAX('Table2'[Date])
VAR bDate = sDate-90
VAR sValue = CALCULATE(AVERAGE('Table2'[Count]),FILTER(ALL('Table2'[Date]),'Table2'[Date] >= bDate && 'Table2'[Date] <= sDate))
RETURN sValue

     

     

     

    Change the "Calculate(Average" to a Max or Min for the other two measures and get the MAX Date.

    90 Min = 
VAR sDate = MAXX(ALL('Table2'[Date]),'Table2'[Date])
VAR bDate = sDate-90
VAR sValue = CALCULATE(MIN('Table2'[Count]),FILTER(ALL('Table2'[Date]),'Table2'[Date] >= bDate && 'Table2'[Date] <= sDate))
RETURN sValue

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you so much for your quick response. 

 

I have updated the code to the following:

 

Moving Average = 
VAR MyIndex = SELECTEDVALUE('Proposals'[Index],MAX('Proposals'[Index]))
VAR myResult = 
    AVERAGEX(
        FILTER(ALL( 'Proposals'),
            'Proposals'[Index] > MyIndex-13  && 
            'Proposals'[Index] <= MyIndex
           ),'Proposals'[Proposals]
           )
RETURN FIXED(myResult,2)

 

Unfortunately, the moving average column is all coming back as "0.00"

 

Is there an easy way to share my Power BI file?

 

BrianConnelly's avatar
BrianConnelly
Icon for Resolver III rankResolver III
3 years ago

You have to upload to GIT or Onedrive and share Publicly the link.  You could also past sample data and image of the dataset.