Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter a report using query string for relative dates

Hi,   Is there a way pre-filter dates? I want my data to get filtered by rolling 30 days.  I want the user to have an option to change the filter if needed and look for broader time period. But by...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

    How about to use filter on this visual?

     

     

    Days = 
VAR x = 
CALCULATE(
    LASTDATE('Table'[Date]),
    ALL('Table'[Date])
)
RETURN
DATEDIFF(
    MAX('Table'[Date]),
    x,
    DAY
)

     

     

    Even if your data is updated daily, only the last 30 days will be displayed.

    If you want to display longer data, you need to modify the filter.

     

    Best regards,
    Lionel Chen

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

Aron_Moore's avatar
Aron_Moore
Solution Specialist
6 years ago

Sure. I have a few reports that filter "Current Month" or "Current Quarter".

 

I just build a custom column that checks like

IF('Executive Report'[Fiscal year / Qtr]=YEAR(TODAY())&" - Q"&ROUNDUP(MONTH(TODAY())/3,0),"Yes","No")
 
Then I just save the report with the filter selected to "Yes".
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks! Can this filter be removed by the customers later, if they want to expand the date field? 

  • Aron_Moore's avatar
    Aron_Moore
    Solution Specialist
    6 years ago

    Yup. The filter visual looks like this:

     

    They can just clear the filter and it'll open the report to all month, quaters, etc.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
       

      I see! But you have made buckets in your time. Mine is a date slicer like below.  I want to provide 30-day dynamic filter.

      • Aron_Moore's avatar
        Aron_Moore
        Solution Specialist
        6 years ago

        Yes, I made buckets and that's what makes it dynamic.

         

        Whenever the report is refreshed the calculated column is recalculated potentially moving the current quarter.

         

        You'll need to do something similar and switch from the Date slicer to a 'regular' slicer to handle the dynamism.