Average of 3 Months values (over more than a year period) - considering filters APPLIED

[Alexa1104]

Hello,

I have a huge table with a lot of information but only some columns are related to this topic. And so it begins:

every month I have a new "x" data. This X is calculated like this : 

x= CALCULATE([Duplicates]/COUNT(ReqTestResults[Merged]),ALLEXCEPT(ReqTestResults,ReqTestResults[Startedby],ReqTestResults[Testname],ReqTestResults[mm-yy]))

And in the graphic above I show its avarage per month. 

My problem is that I should show in the same grapfic (with the help of a new variable) the avarage of the last 3 months . so for ex in June it should be a value of (31.38+32.85+31.45)/3 = 31.89 %.

I don;t know how to do this since when I try to make an avarage, it doesn't take into considerations the filter which I applied above in order to calculate x (filters applied on the whole page) :

  I would really appreciate your help..thank you in Advance!

One of the solution I have tried is :

3mth = CALCULATE(AVERAGE(ReqTestResults[_Duplicated_Work(Duplicates/All Tests including duplicates)]),ALLEXCEPT(ReqTestResults,ReqTestResults[mm-yy],ReqTestResults[Testname],ReqTestResults[Startedby]),DATESINPERIOD(ReqTestResults[mm-yy],LASTDATE(ReqTestResults[mm-yy]),-2,MONTH)). But it doesn';t work 😞

[Alexa1104]

Thank you very much! you solution worked perfectly!

[Joe_Barry · Accepted solution]

Hi @Alexa1104 

If you have a Date Table in your data model, you can try below. Replace the [ThisTable'sDateColumn] part with the actual date column from the table. [x] is the count measure. Provide a sample of the table with columns ames and maybe I can taylor the emasure better for you

Joe

Rolling 3 Month Average = 
VAR NumOfMonths = 3
VAR LastCurrentDate =
    MAX ( DIM_Date[Date] )
VAR Period =
    DATESINPERIOD ( DIM_Date[Date] , LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( DIM_Date[Date]  ),
            [X]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, DIM_Date[Date]  )
VAR LastDateWithViews = MAX ( 'ReqTestResults'[ThisTable'sdateColumn] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithViews, Result )