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-...
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.

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thanks very much. I think this is now working exactly how i need it to. Out of curiosity how does the measure know to choose the month and not the day or year?

        Also i can use the same measure to create another filter for date in the next 6-12 and 12-18 just by copying this measure and changing the 3 and the 6 to 6 and 12?

        Thanks

        Alex