Forum Discussion

alexpetersen's avatar
alexpetersen
Frequent Visitor
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

     

     

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

Change MyIndex to 

VAR MyIndex = SELECTEDVALUE('Proposals'[Index],<A Default Value>)

 

A default value can be something like MAX()

MAX('Proposals'[Index])

 

Change your filter to

 

FILTER(ALL('Proposals')....

 

alexpetersen's avatar
alexpetersen
Frequent Visitor
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.