Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Future Date Filtering

Hi,

 

Im looking for some help with creating a filter to apply to my report.


I have a field called DUEDATE and i want to filter my report results to show only where the DUEDATE is in the next 3-6 months.

 

For example if today is 28/10/2020 i want the report to only show due dates that are due in the next 3-6 months so from 28/01/21 to 28/04/21

 

I have created one report already which was a report that only shows DUEDATE in the next 3 months which i used the relative date filter (easy enough) but its the 3-6 month and 6-12 month reports im finding difficult.

 

Any help would be apprechiated

 

Thanks

 

Alex

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    As my previous note in the formula, you can use today() function to replace the specific date in the formula:

    Measure = 
IF (
    SELECTEDVALUE ( 'Table'[Date] ) >= EDATE ( TODAY(), 3 )
        && SELECTEDVALUE ( 'Table'[Date] ) <= EDATE ( TODAY(), 6 ),
    1,
    0
)

    Result:

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

    measure =
    var _max1 = maxx(allselected(Date),Date[Date])
    var _max = date(year(_max1), month(_max1)+6, day(_max1))
    var _min = date(year(_max1), month(_max1)+3, day(_max1))
    return
    calculate([Measure],filter(Date, Date[Date] >=_min && Date[Date] <=_max))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak ,

       

      Thanks for your reply.

       

      I was recieving errors below where its highlighted in bold:-

       

      measure =
      var _max1 = maxx(allselected(Date),DUEDATE[DATE],
      var _max = date(year(_max1), month(_max1)+6, day(_max1))
      var _min = date(year(_max1), month(_max1)+3, day(_max1))
      return
      calculate([Measure],filter(Date, Date[Date] >=_min && Date[Date] <=_max))

       

       

       
       
  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi Anonymous ,

    You can try to use EDATE() function to create a control visual measure, set its value as 1 and put it in the visual filter:

    Measure =
IF (
    SELECTEDVALUE ( 'Table'[Date] ) >= EDATE ( DATE ( 2020, 10, 28 ), 3 )
        && SELECTEDVALUE ( 'Table'[Date] ) <= EDATE ( DATE ( 2020, 10, 28 ), 6 ),
    1,
    0
)
// the date in the edate() function can be any and today(), depends on your need, this is just an example

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, Thanks for this.

       

      This is working and when i apply the filter to equal '1' it is showing the dates that are 3-6 months from 28/10/20.


      Instead of inputting a specific date into the EDATE how would i apply the date to just be TODAY as this would then constantly change the filter everyday as i would like this to be used for a long time so as a tender expected date ages it moves from 0-3, 3-6, 6-12 so on and so forth.

       

      Basically how do i get the date to just be from today instead of 28/10/20.

       

      Thanks very much for your help this has been frustrating to get the right solution

       

      Alex

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        Hi Anonymous ,

        As my previous note in the formula, you can use today() function to replace the specific date in the formula:

        Measure = 
IF (
    SELECTEDVALUE ( 'Table'[Date] ) >= EDATE ( TODAY(), 3 )
        && SELECTEDVALUE ( 'Table'[Date] ) <= EDATE ( TODAY(), 6 ),
    1,
    0
)

        Result:

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.