Use date filters on visual to bring in calendar years plus current incomplete year

[JemmaD]

Hi there 🙂

I know it is possible in query editor to do a 'last n calendar years' AND 'this year' but I want to achieve this in a visual.

So I can apply the last n calendar years, but it excludes the current incomplete year - and there is no option for an AND in the visual level filters pane. 

Is there a way to achieve this?

[johnt75 · Accepted solution]

The inbuilt relative date filtering is pretty bad. The only way I've found to do this sort of thing in the past is to add a column to the date table which returns true or false depending on the logic you need, e.g.

Is in last 4 years =
var startYear = YEAR( TODAY()) -5
var endYear = YEAR(TODAY()) - 1
return 'Date'[Year] >= startYear && 'Date'[Year] <= endYear

[JemmaD]

Because my Year is a text column I had to adapt the calculated column to:

Previous 4 Years =

VAR Year = VALUE ('Dates'[Year])
VAR startYear = YEAR ( TODAY ()) -3
VAR endYear = YEAR( TODAY ())
RETURN
Year>= startYear || Year = endYear