Filter table based on rolling date range.

[peterg417]

Not sure if a DAX calculation is the way to do this (probably isn't) but in trying to figure out how to do this, this is the path that I went on.

One of my analysts wants to view leases within a table whose firm date falls within a certain range (between 2 and 3 months ago).

She also wants this to be dynamic. So today she wants to see leases that have a firm date between February 1st and February 28th. But when she looks at the table on August 1st, she wants to see leases that have a firm date between May 1st and May 31st.

My initial idea was to create a measure that did this:

LeaseFirmDate 2-3 months ago =
SUMX (
FILTER (
Lease,
Lease[LeaseFirmDate]
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 60 )
&& Lease[LeaseFirmDate]
> DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 90 )
),
Lease[Leases Count]
)

When I drop this measure into the table, it gives me a result of 1 if the item matches that criteria so then I can set the Visual Level filter to equal 1 and voila! It works, however...

Problem is that it takes forever for the data to update. So if I want to see leases in a different region it takes almost 2 minutes for the data to change.

Is there a way to do this? I can't do a rolling date filter in the Filter Pane. It only allows me to enter a specific date.

[TomMartens · Accepted solution]

Hey,

I'm not sure if I understand you requirements 100%, but as a 1st attempt I recommend to rewrite your existing measure a little:

LeaseFirmDate 2-3 months ago =
var Date1 = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 60 )
var Date2 = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 90 )
var t = 
	FILTER(
		Lease,
		Lease[LeaseFirmDate] < Date1 && Lease[LeaseFirmDate] > Date2
	)
return
SUMX(
	t
	,Lease[Leases Count]
)

if this does not work, can you please provide a pbix that contains sample data and represents your data model. Upload the file to onedrive or dropbox and share the link.

Regards,

Tom