Rolling Average calculation

[tonny_bwg]

Dear all, 
I had an issue when calcuating rolling average. the senario is as follow:
I created two time tables (dim_date & dim_date2) with inactive relationship. dim_date has 1:many relationship to the fact table.  I used yearmonth of dim_date as slicer on the page, the chart X-axis used dim_date2[Yearmonth] to display the period. when  I put the rolling avg meansue to Y-axis, only one month value but 6 month avg is displayed. the dax I used as below:

Revenue = sum(revenue[balace])

rolling avg_6 =

  CALCULATE(AVERAGEX(VALUES(dim_Date[YearMonth]),[Revenue]), REMOVEFILTERS(dim_Date[YearMonth]),

             DATESINPERIOD(dim_Date[Date],max(dim_Date[Date]),-6,MONTH))

Last_N_ Revenue_Rolling_6 =

    VAR _number = 6

    VAR _maxdate = MAX(dim_Date[Date])

    VAR _lastperiods =

          DATESINPERIOD(dim_Date2[Date],_maxdate,-_number,MONTH)

   Return

    CALCULATE(

         AVERAGEX(VALUES(dim_Date[YearMonth]),[rolling avg_6]),

          REMOVEFILTERS(dim_Date),

         KEEPFILTERS(_lastperiods),

        USERELATIONSHIP(dim_Date[Date],dim_Date2[Date]))


above pic shows that danymic period based on slicers value, Bar chart shows the value for selected periods. I want to add line to chart to show rolling average of 6 months for the value in selected period only.  

thanks for your help.

--Tonny

[lbendlin]

Please read about the new windowing functions. They make this much easier.