Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

FIltering table on relative date

I have a table of tasks with a field start date. I wish to show a table of tasks with start date in the last 30 days or the next 90 days?

How do i do this? I guess i need to add a calculated column that returns 1 when in the correct date range and then filter the tabel on that calulated value, but not sure how to build the formula.

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    You can create measure Filter1 like DAX below, then put the Filter1 in the Visual Level Filter of table visual which displays the tasks , setting Filter1 as "is not blank".

     

    Filter1=IF (MAX ( 'Date table'[StartDate] )>=TODAY()-30 && MAX ('Date table'[StartDate])<=TODAY()+90, 1, BLANK())

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    6 years ago

    yes you will need a colunm unfortunatly relative date filters in only go in one direction and there is not option for an OR clause in the filter

    this colunm should get you what you want 

     

    Relative date = 
var lastdays = 30
var nextdays = 90
var last_date = TODAY()-lastdays
var next_Date = TODAY()+nextdays

var rd = IF(and(Table1[date] >=last_date,Table1[date]<=next_Date),true(),false())
return rd
  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You can create measure Filter1 like DAX below, then put the Filter1 in the Visual Level Filter of table visual which displays the tasks , setting Filter1 as "is not blank".

     

    Filter1=IF (MAX ( 'Date table'[StartDate] )>=TODAY()-30 && MAX ('Date table'[StartDate])<=TODAY()+90, 1, BLANK())

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.