Forum Discussion

WestWinter's avatar
WestWinter
Icon for Helper II rankHelper II
6 years ago

Moving Averages for Measures

I wanted to create a moving average line and plot it against my measure that rebases my prices to 100. My moving average measure originally did work with my original Return index column, however, it did not work with my measure for rebasing the Return index. Was wondering why this so....

 

Moving Average Formula (50D):

50D MAV = CALCULATE(
    AVERAGEX('RI','RI'[Asset Price Rebased]),
    DATESINPERIOD('DateTable'[Date],
    LASTDATE('DateTable'[Date]),
    -50,
    DAY))

 

Rebasing DAX Measure Formula:

Asset Price Rebased = VAR Date_Index = CALCULATE(MIN('RI'[Date]),ALLSELECTED('RI'))
Return
(SUM('RI'[Return Index]) / (CALCULATE(SUM(RI[Return Index]), 'RI'[Date] = Date_Index))) * 100
 
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    WestWinter , Try lik

    50D MAV = CALCULATE(
    (SUM('RI'[Return Index]) / (CALCULATE(SUM(RI[Return Index]), 'RI'[Date] = Date_Index))) * 100,
    DATESINPERIOD('DateTable'[Date],
    LASTDATE('DateTable'[Date]),
    -50,
    DAY))

     

    But better to divide it count of index or date

    Example
    50D MAV = CALCULATE(
    divide(SUM('RI'[Return Index]) , (CALCULATE(Count(RI[Return Index])))) * 100,
    DATESINPERIOD('DateTable'[Date],
    LASTDATE('DateTable'[Date]),
    -50,
    DAY))

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi , WestWinter 

        Is it possible to share that sample pbix with me to test, or show how your table looks like. I am curious  what the error message is. Why it doesn't work?

         

        Best Regards,
        Community Support Team _ Eason