cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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) :

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 😞
1 ACCEPTED SOLUTION
Super User

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 )``````

Proud to be a Super User!

Learn about the Star Schema, it will solve many issues in Power BI!

2 REPLIES 2
Regular Visitor

Thank you very much! you solution worked perfectly!

Super User

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 )``````

Proud to be a Super User!

Learn about the Star Schema, it will solve many issues in Power BI!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.