Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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
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
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |