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.