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

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

 

 

alexpetersen's avatar
alexpetersen
Frequent Visitor
3 years ago

Thank you! 

 

Would you mind posting your power bi file or picture of your data model for me to replicate. I am having issues getting it to work on my end?

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

    I didn't use a data model, just made a copy of your posted data.

     

    The measures are posted in previous.  If you have a date table with a relationship then switch out the date fields in the measure with the date fields of the date table.

     

     

    Also, check your data types, i.e. Date fields are of data type Date.  Numbers are of data type Whole or Decimal, etc.