Forum Discussion
Resolver IIRelative Date Filter for 4 years PLUS YTD
Hello. I have a date table for which I would like to filter only current YTD plus 4 years. When I use the relative date filter, I get the choice of the past 5 years (9/14/2015-9/14/2020) OR the past 5 calendar years (1/1/2015-12/31/2019). What I want is 1/1/2016-9/14/2020, so four full years plus calendar YTD. Then I tried to put in an advanced filter for the Year field >= YEAR(TODAY()) - 4, but that didn't work either. Any other thoughts? I appreciate any tips!
dkernen, if you want to filter the entire visual, you can use the measure below. Add this measure to the visual filter and select "is 1". The date field in the visual should be from the date table. No date slicer is required since it's based on today's date. If you have multiple measures in the visual, this will filter them all (assuming your date table is joined to each fact table referenced by the measures).
Is YTD or Last 4 Yrs = VAR vToday = TODAY () VAR vYearOffset = 4 VAR vCurYear = YEAR ( vToday ) VAR vStartYear = vCurYear - vYearOffset VAR vResult = IF ( YEAR ( MAX ( 'Date'[Date] ) ) >= vStartYear && MAX ( 'Date'[Date] ) <= vToday, 1, 0 ) RETURN vResult
6 Replies
- DataInsights
Super User
dkernen, if you want to filter the entire visual, you can use the measure below. Add this measure to the visual filter and select "is 1". The date field in the visual should be from the date table. No date slicer is required since it's based on today's date. If you have multiple measures in the visual, this will filter them all (assuming your date table is joined to each fact table referenced by the measures).
Is YTD or Last 4 Yrs = VAR vToday = TODAY () VAR vYearOffset = 4 VAR vCurYear = YEAR ( vToday ) VAR vStartYear = vCurYear - vYearOffset VAR vResult = IF ( YEAR ( MAX ( 'Date'[Date] ) ) >= vStartYear && MAX ( 'Date'[Date] ) <= vToday, 1, 0 ) RETURN vResult - amitchandak
dkernen , You should last select last 4 year including today
and have measure like this with date table
Measure =
var _min = minx(allselected(Date), startofyear(Date[Date]))
var _max = maxx(allselected(Date), Date[Date])
return
calculate(sum(Table[Value]), filter(all(Date),Date[Date]>= _min && Date[Date]<=_max))
dkernenThank you. This measure works beautifully. However, how do I add a measure to the visual filters, as it appears to only accept data fields?
- DataInsights
Great! In the Filters pane, section "Filters on this visual", drag the measure to the "Add data fields here" box.
