Relative date filtering, need last 3 years PLUS YTD.

[Anonymous]

I am trying to filter my data for my reports.  I need the last full 3 years plus this YTD.  Today is May 4, 2018.  So I would want to see 1/1/2014 - 5/4/2018.

[ParalleloSam]

I know this has been marked as solved but for other people searching for answers, the way I solved this was by using the filter type of Top N values against my Year column (I created a year column based off my date column). I needed last year and YTD values, so I used the Top 2 values by my year column. This gave me all data for last year and this year to date. I hope this helps someone in the future

[Anonymous]

I love the simplicity of this solution. 👍

[Greg_Deckler]

Perhaps something along the lines of:

Measure = CALCULATE(<something>,FILTER(ALL('Calendar'),[Date]>=DATE(YEAR(TODAY())-4,1,1)))

[Anonymous]

That is a measure, I can't use that in a relative filter for the page or the report.  Thanks for trying!

[Greg_Deckler]

@Anonymous- This is why you need to better explain your data and what exactly you are trying to accomplish. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Ok, let me try again. I need a relative report filter. I have too much data. When using my Calendar table, I am able to do a relative filter, which is nice, but does not do what my users expect. Currently Report Level Filters are on Calendar.ADate, which is type DateTime. Filter Type is relative. Show items when the value: is in the last 4 calendar years. This gives me 1/1/2014 - 12/31/2017. My expect result is that I will see data between 1/1/2014 and today (5/7/2018). Thanks again for your help.

[Greg_Deckler]

@Anonymous- The relative date slicer doesn't work that way. You can do Last x Years (current date to current date) or Last x Years (Calendar) but not what you are asking. Therefore, you are going to have to change the filter context of your visual(s). Easiest thing to do would be to use Year (Calendar) mode and then just wipe out anything that is greater than TODAY().

[dbhudia · Accepted solution]

I know this is an old thread but putting this on anyways. 

This 'Should' be something relative date could do but sadly doesn't. I created a calculated field on my date table called YearIndex

So the current year is 0 and the prior year is -1. Now you can just use the basic filter to get Current Year and Last year or however many years you want. 

YearIndex =

   VAR ThisYear = YEAR(NOW())

   RETURN

   Dates[Year] - ThisYear

[MatMatt]

Amazing Solution! Thank you!

[Anonymous]

I logged in, just to be able to give you a thumbs up! Your solution is perfect for me.

[Anonymous]

Thank you! Solved my issue, exactly what I was looking for. Should be chosen as solution.