How to calculate average if between a certain date range?

[jsabo]

Hey guys,

Brand new to DAX and I am looking for something similar to an AverageIF...

I have a number of documents which occurred over 5 years. I would like to average how many occurred over 12 months, 24 months, etc.

So I need the DAX to be the equivalent of =AVERAGEIF the date approved falls within 12 months back from today, 24 months back from today, etc...

Any ideas? Thanks.

 

[Matt Allington]

I don't know what the average portion of the formula is because you haven't described your data. However you can use calculate to filter the table so it only contains the date range you need. But what that formula looks like depends on your calendar table and the logic of a business year.

Do do you have a calendar table? If not, you should create one. Power Pivot Calendar Tables - Excelerator BI
if it passes "the rules" then you could write a formula like this.

=calculate([your avg formula],datesbetween(calendar[date),dateadd(calendar[date],-1,year),lastdate(calendar[date])))

otherwise you you could write something similar with filter