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 default when the user opens the link he should see only 30 days since those are most relevant. 

 

My report is automatically refreshed everyday.

 

Regards

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.

7 Replies

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

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    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.