Relative Date Filtering

[JDTAYLOR]

Relative date filtering works for showing values within a time period (i.e. "Last 90 days", Last Year") However, is it possible to filter values outside of a selected time period? For example, I have a work order database. I would like to display all work orders older than 90 days. How could I accomplish this? I am using Direct Query mode so creating a custom is presumably out of the question. 

[v-diye-msft · Accepted solution]

Hi @JDTAYLOR 

You might try this measure:

Outoflast90days=

var a = SELECTEDVALUE('Table 2'[Date])
Return
IF(MAX('Table'[Date])>=a-90&&MAX('Table'[Date])<=a,0,1)

table2 is a calendar table, filter the measure with 1, and then you can get the results.

[amitchandak]

@JDTAYLOR ,Assuming date in slicer is from the date table. Try a measure like

Measure =
var _min = minx(date,date[date])
return
calculate([Measure2],filter(all(date),date[date]<=_min))

[JDTAYLOR]

Thank you for the quick response @amitchandak! What is the significance of "measure2" in your formula?

Here is what I have so far.

Greater Than 90 Days = var _min = MINX(WKORDER,WKORDER[WO_INIDATE]) return calculate([Measure2],FILTER(ALL(WKORDER),WKORDER[WO_INIDATE]<=_min))

[amitchandak]

[Measure 2] is any calculation you want to use

count(Table[ID]) or sum(Table[Value])