Dynamic Date Range using Max

[#1]

I want to create a formula that will return the average of sales within 30 days of the latest date in the period I select within my app.  I have tried several approaches, but they aren't giving me the result I would expect.

[sunny_talwar · Accepted solution]

May be like this:

Avg(Aggr(Sum({<DateField = {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>}Sales), DateField))

[#3]

Sorry - this actually looks a bit off.  Not 100% sure what's driving the outage, but the Sales amounts in the for the dates in question are pretty different in the visualization where I use that formula vs. a basic sum of the Sales for the same period in a basic table.  I'll dig in and let you know if I determine outage.

[sunny_talwar]

What are your chart dimensions?

[#5]

Using your suggestion, I believe I would be using:

Avg(Aggr(Sum({<[CloseDate]= {"$(='>=' & Date(Max(Date) -30, 'MM/DD/YYYY') & '<=' & Date(Max(Date), 'MM/DD/YYYY'))"}>}[Bookings]), [CloseDate]))