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
    6 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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Perhaps something along the lines of:

 

Measure = CALCULATE(<something>,FILTER(ALL('Calendar'),[Date]>=DATE(YEAR(TODAY())-4,1,1)))
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

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