Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Relative date filtering, need last 3 years PLUS YTD.

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.
Need Help
  • dbhudia's avatar
    dbhudia
    5 years ago

    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
ParalleloSam's avatar
ParalleloSam
Frequent Visitor
4 years ago

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's avatar
Anonymous
Not applicable
2 years ago

I love the simplicity of this solution. 👍